Driving you nuts there no worries i was like what's wrong with my microphone i have like five nights out i was like that was great totally my bad totally my bad so again sorry everyone uh the the one minute delay was was my fault um but anyways welcome to excel today or you know it's our our weekly show here on uh on learn it training where we you know we're gonna give you guys tips tricks and everything like that and also give you updates on what's upcoming and.
Everything like that um just some quick updates guys uh just on the channel overall uh we have uh powerapps uh tomorrow we're super excited about that because we've seen a lot of people been uh looking for powerapps tutorials lately so uh this one was done by trish connor cato and she really did a great job on this one so i hope um i hope you guys are uh you know learn a lot from it or looking forward to it because it is something that we got a lot of requests for so yeah we're super jazzed about that one uh also i have been interviewing people.
Non-stop everybody to get uh new people and new topics and everything like that upcoming uh right now we're we have a heavy focus on the apple product line so all you apple users we are going to be bringing out um was that office for mac and also we're going to be covering keynote pages and numbers as well so we getting all those in the works i have a lot of great guys that are also doing the sql service the sql service reporting stuff uh so we're going to get that class created as well that's going to be a about a four to six video series just.
Depending on what the content looks like but i have some really strong instructors coming you guys and also joe you just got a mac from my understand and so i think we're going to have you do a few of those uh classes for us oh yeah the name back bro that's so nice so yeah so you guys those that's what's going to be upcoming on the channel um and again if you guys have anything you want to learn please just drop it in the comments and the chat wherever um we.
Will i i go through every single comment that comes through the channel and we i mark it all down on which ones are getting requests and everything like that so that we you know that we're delivering stuff that you guys want so you know just let us know and we will try to get it made um and then also oh yeah also i'm hoping that uh another person who i interviewed for the g suite stuff wants to do it um they they seem incredible so i'm hoping that we'll also have the full g suite and that means all not just you know docs pages but all those other uh programs that google has released lately.
Learner jasmine and micah thanks for joining us looks like excel learners from cambodia hello from capodia and then mica's from kenya so good morning guys thank you for joining us today joe what are we going to cover today i almost forgot i've been talking so long hey today's gonna be a lot of fun um so we are gonna go we're gonna take some data we're gonna put it into power bi but what i wanna show you is that you know power bi sometimes is limited with designing it to look good so i'm gonna show you how we can create a design.
And then load it into power bi so we can use it as our backgrounds and also i wanted to touch base on conditional formatting right because now a lot of people know within our visuals we actually can use conditional formatting based off of the criteria or condition the appearance will change of the different colors of the bars and we'll set up that logic um you know in time so if you're following along with me click on that file button and then we will we'll kind of build this together um but before we get into that of course mugs we have.
Those i got a really cool one today i i was pretty excited about this one it says because i know it's probably backwards it says i'm trying to be awesome today but i'm exhausted from being so freaking awesome yesterday nice nice well you are awesome joe i have uh this is my real life mug i use this morning this is just not just something i bought coming in hot this is one of my favorite ones just because of the size hopefully you didn't see all the coffee scenes on it but yeah i drink a lot of coffee guys so that's why i'm always amped up here.
[Laughter] um just say hello to a few other people here um rajesh she has a question already even lou naiman jstar haptamu good morning from the bay area maybe it's good evening good afternoon i know the time zones are all different but i hope you guys are have had a great day are gonna have a great day um yeah you ready to jump into this joe and again the exercise files is in the chat let me drop it again for you guys just so you can follow along there you go.
All right i'm gonna share my screen and let's start off with our sales report so i'm just going to go into the sales report real quick you don't have to open this up with me but i just want to show you what we have here and you know i used my two favorite people at learn it so of course it's going to be a couple of sales reps like me liz and then ben now what we have is some you know simplistic data nothing too crazy here we have the date of the cell uh who the sales rep was and then if they actually.
Sold or if they actually gained loss on the sell right because sometimes that
Happens you'll you know maybe comp something out or you know maybe um go over promise on a package it happens in sales so sometimes we have to have that loss in there and then of course the location that they were selling in now what i want to do is i just want to create a nice little report for my higher up my ceo that will display our gain loss right uh we'll maybe display.Something like slicers so that we can change all the information maybe a pie chart or a gauge chart that will show our overall sales so that's what we're gonna do today now we do have to still load this into the power query just to make sure that the data is good so i'm just gonna exit out of here and open up my power bi and then once we load it in we'll transform it in power query of course we always want to do that first so at this point i like to keep this screen on by the way everyone you can turn this off right down here if you.
Wanted to so it doesn't pop up every time but just keep in mind this is where like all the new stuff will be shown it'll tell you what's new today what's been improving power bi really is updated like every month there's always something new in there i love it it's just they're really consistent with keeping this the most up-to-date and then you can go into like different forms and stuff so i do like this page popping up i don't mind clicking the little x but it's an option so since this is an excel file i'm gonna.
Click import data from excel i'm gonna choose my sales report wherever you saved it of course you can look through your computer here and i'll click open and even though it's gonna look pretty good the data i i set this data up pretty nicely like our headers don't need to be pushed up our data looks good you should always just scroll a little bit look at your data never just load it in there's always going to be something right unless you're like really a perfectionist so if i'm looking through.
This data set let's see there it is we have a bunch of null values so that can really mess up our visuals that's why i always say double check so i'm going to click on transform data to pull this into the power query of course now i just want to show you this this is just a tip it's not really what i'm going over today but you'll notice right up top here you'll see these little lines some of it is green some of it is like darker gray those are really important because it'll tell you a.
Percentage of valid data inside of your columns it'll also tell you a empty amount of data or if there's any errors in it and not only that but this little box will also give you a way to actually remove those empties so if that's something that you're trying to do just remove all the empties you're more than welcome to quickly so it's really just analyzing your data giving you information about it and also giving you a solution if it finds there's something wrong so just i would use that and i just simply get that by hovering over that.
Bar if you ever see dark in the bar and it's not fully green that's a problem that means there's missing data and that's never good for your visuals so to fix this problem all i'm going to do is simply go to remove rows which can be found on the home tab here and when you click on remove rows you'll have an option that says remove blank rows and that's the one i'm going to choose there it is remove blank rows and look at that we have green going fully across each of these so now i know.
There's no missing data if you want to just hover over again it should say a hundred percent there we go there's 99 valid data pieces in there and there's 100 of it has valid data now that this is looking pretty great i'm just going to close and apply this and bring it into of course my power bi it's going to load it in i don't like how it's called sheet one i you know it really is based off of what you have in the original file so if you.
Name your tabs which is a great idea in excel um you know it'll save you a lot of time in the future so we can fix this though we could click the three dots right next to it and we could just simply click on rename and then i'm just going to name this uh like sales data there we go just in case and i'm doing this by the way just in case i need to pull in other pieces of data and imagine if there were like i didn't name many of my tabs and there was like sheet1 sheet1.
Sheet1 that would be very confusing so it's always important to name these
Now if you click the little carrot here it will show you all your different fields we have our date fields right date hierarchy fields which will show you year quarter month day which is really nice and you have your gain loss which is a numeric value that little new numeric value symbol that's important that shows you that in that column itself they're numeric and then location and sales are up.Now i do want to make this look a little more pretty and yeah there are ways to do it like i could start to insert you know different shapes and images but it's really not as i don't know it's not as easy as you could do it in other applications so i'm actually going to just minimize my power bi for a second and go into powerpoint i know everything's about the power right power pivot power bi powerpoint power query um so let's go to powerpoint.
And i'm gonna open up a blank presentation and then i'll just insert like a blank slide i'm thinking and i'll just delete that one so with this blank slide what i want to do here is start to design how i want the background to look on my dashboard and you can do anything you want i'm going to make it look really cool so i'm going to insert maybe like a square up top or a rectangle there we go.
Um i might change the color a little bit maybe i want to do like a yeah that looks good same thing with the shape outline and you can pick whatever colors you like for all this stuff i might do uh yeah this might look good oh i like it i kind of like how it you know boards out like that um okay so we have that that looks good i might want to change the color of the entire actual background here so i'm just going.
To insert another rectangle going across the entire thing here we go but don't worry we're gonna change this we're gonna move it to the back right so i'm gonna just send it backwards or you can click send to the back to send it like all the way to the back and i want you to think of that as like layering like on a book right you're just taking this this piece and just moving it to the back of the book so i'll click back and you'll see now this comes back up top.
And then i'll just shape fill it and i'm going to use like a gray color i might even put some uh gradient on there oh that's not gonna look real nice i like let's do i like this one where it's like a little darker on the bottom and then it goes up so i'm going to use that one and by the way you don't have to do exactly what i'm doing i'm just creating these things just to look good i may also want to maybe add some sort of like icon so i'm gonna insert.
An icon and i'm gonna think um i usually like like something that i don't know maybe is relative to the data like i'm gonna just do this because i'm shining the light on our gains or losses so i'll do maybe a little light bulb here and i'm gonna change that to yeah there we go the gray and i think.
I'll just do one more thing over here a couple of just little bubbles doesn't have to be crazy i'm going to use ctrl v to duplicate those make it a little smaller here maybe duplicate that one again just make some bubbles and i think that that's gonna look great maybe one more we're becoming designers that's what we're doing we're designing this.
To look really cool i'm gonna make this one really small there we go okay and you can do this any way that you want to you know you you could build this up any way that you want and i'm going to just change the colors on those i'm going to make those a little different color and let's do oh that looks great okay and then maybe on the effect i might do like a little soft yeah the soft edges look great.
Let's do that one okay so just a couple of bubbles moving down kind of like where uh who is that guy ross right bob ross we're painting our own little world here all right so uh we have some of these bubbles looking good and now what i want to do is i want to save this and i want to save it to not a powerpoint file but actually i'm.
Going to save it as an image right and you can do that you can save your slides as images so i'm going to add a couple more of these going down and then i'm going to save this as an image okay so to do this you're going to go to file save as i'm just going to save it on my desktop so it's easier for me to find it but i'm not saving it as a powerpoint.
Presentation i'm actually going to click on this and i'm going to change it as a you can use either one i'm usually use jpeg but you can use a png as well but i'm going to do jpeg for today once you click on jpeg click save it'll ask you do you want all the slides i mean but there's only one slide so you could choose either or but i'm just going to use the side that i'm selected on.
And there we go now i'm going to go back to power bi here and now that we we don't have any visuals yet so when you don't have any visuals and you click on the little formatting button here you're actually going to get formatting for the entire dashboard right like the page information page size page background and that's the one that we really want is the page background here i'm going to click the little drop down.
And instead of using any like just color which you're more than welcome to by the way you can change like the whole color of it but instead of doing that i'm actually going to add an image and i'm going to add my image that i just created now nothing's there you're probably wondering joe what happened well you have to change the transparency right now it's at 100 turn that all the way off and there it is so look how cool that looks right i'm.
Going to do maybe a little lighter or you could go darker whatever you like i like it like that okay so now we have our background for our visualization once again you can spend a lot more time on this i know this was just a quick little design thing um you know i i'd probably spend more time and if it was for a real presentation i probably wouldn't have bubbles i would probably make it a lot more smoother on the design but we're doing this for fun right now so why not so we have this now background looks.
Great i might want to add a text box right and in this text box i'll probably put something like um sales report 2022 something simple i'm going to make that a little bigger we'll do 40. i'm going to make it white and i'm going to just go like this now when you do this you're going to notice when you click away from this thing you're gonna be like joe i can't see my text yeah that's because you have.
To go into the format text box right here that pops up and turn off the background right now there's a white background so when you turn that off then you'll see now it's just transparent and then you can just move this around right next to a little light bulb we did before awesome all right so we have our sales report 2020 now we need to get to the visuals so i'm going to build a couple of visuals here i'll probably do i don't know i want to do the gauge like i said before we'll do a gauge and that.
Will just be the total of our win loss here or gain loss i i really don't like first off the 116k and the 231k i rather see real numbers and i really don't like how big of a gap it is so i'm going to change this from uh you know a total of 231k because it's doing an average to 150k so that this line is going a little further and to do that of course you're going to go to the formatting in your visualization.
Now i will go to the gauge axis here and i'm going to put the maximum as like i said 150 000 just like that so you see that gauge moves over and it now says 150 here another thing i don't like is the data labels i don't like the way it says auto so i'm going to change that to none and then i'm also going to just make sure that there's you know no decimals and i can make it a little bigger that that text too i might go to like.
15 looks good so now it's a little bigger there now i i don't like how it's you know it's really not showing me the little thousands so i'm going to want to fix that little comma there and there's no comma there right now so to do this you usually go into the formatting up top and you'll have a like once you're in there you'll have an actual option to put the comma in there so uh right now i don't have that so that's.
Okay i mean we'll wait on it but you're you can do that you can put the commas and if it really bugs you a lot with the data and that's just really just setting up your data on the other end of things um what else do i want to change i do want to change the callout value as well this middle piece so once again i'm going to change that to a none i'm going to make sure that you know it's zero decimal places and that looks good i'm happy with it so and you can change you know the color.
As well so you might want to change the gauge color let's do i want to do a lighter blue because the rest of the report is pretty light i'll try to match it as as nice as possible let's see a little lighter there we go i think i think that's close enough don't mind me i'm perfectionist when it comes to this okay so.
Now i have my one table here i might want to add some borders to this so i'll turn on the border and i might want to do rounded borders totally up to you you can do a radius border i typically like the look of the radius borders but it's not for everyone now the next thing i'm going to do is i'm going to create another report for this one i'll probably do i don't know maybe a map and then we can take the location put it into location and we'll take the gain.
Loss and put it into the size of our bullet points now it might not look like much right now because it has united states right there and we have our three spots but trust me when we like start to filter for the locations it will zoom into the map pretty cool and once again i'm probably going to change the border there turn it on and put the border on a radius and there we go now the last thing that i'm going to do.
Is i want to create another chart right here right that's what i'm envisioning one more chart here that shows me like a clustered column and then i'll put in a couple of slicers over here so for my cluster column i'm going to use the stacked column chart for my axes i'm probably going to put in the date and it's going to show you all the different dates here you can just choose like i just want to see the months i don't need the years i don't need the quarters i don't need the days and just.
Make it months and then the values are going to be the gains right the gain loss and there we go we have our nice little chart here now there are some losses here there are some gains like and i can't really see that as well as i want to so i do want to see these negative values because right now everything looks the same even though those are below the zero so this is where my conditional formatting is going to come into play all right i'm going to first off change the border again i'll turn the border on.
Give it a little radius there we go and for this one i'm now going to baste these colors right and i can do that off of formatting i can base those colors those data colors off of what's known as fx which will apply conditional formatting so i'm going to click the little fx next to the data color and it's going to say well joe what kind of formatting do you want do you want it to be like gradient you can do that you.
Can set like maximum minimum maximum values but i'm going to say i want to use logic i want to actually use rules to define what i want these colors to be so the first thing is anything that is less than zero i need that to be a nice red i'm gonna make it really really yeah just like that okay so first off all we're doing on this one is changing the color because everything's already set so we've taken care of the less than zero is going to be read now.
We have to add a new rule and we have to say that anything that is greater than or equal to zero make sure it's not a percent but it's a number right anything that is greater than equal to zero and you can't keep zero you'll notice it's going to be grayed out you have to put something here now i'm going to actually just backspace and it defaults to max which means that now you can actually use the conditional formatting but something needs to be filled out on this one and this one.