11 o'clock where i am i have moved back to the east coast because i am buying a house for me and my family which is really awesome so uh yeah congratulations congratulations yes thank you thank you yeah super excited but that's not going to deter us from having these shows and having a lot of fun on them so now we have both coasts covered yeah same coast and east coast so but yeah i'm super excited to be here today talking about power bi i mean power bi is just one of my favorite applications i think i probably teach it.
At least two times a week and it never gets old it's it's just always amazing yeah well the amount of updates they do on power bi it's always new and so there's always something new to learn or something a new feature that maybe they move to another place and sometimes they change the entire the entire ribbon and stuff like that or just like they move one thing to another place and so it's you know it's you always have to be on top of your game when it comes to power bi you always have to be yeah it is the definition of a lifelong learning.
Software that you always have to be on top of foreign let's say like excel you can learn like 80 of it and you'll be good for pretty much ever yeah exactly it's it's just constantly evolving and you just got to be on top of it so it's uh yeah hello yeah oh yeah hello even thank you for joining us and then uh of course i'm gonna get right into the mugs because we've been forgetting to do it so many times today i have uh we were a red hat training partner so i.
Just have a little red hat thing they sent a there's actually a really nice mug this is a yeti one again we're not being we're not getting compensated or anything like that but it's a super nice coffee mug they look pretty sturdy yeah they they were just sending out uh you know uh what you call kits with all their gear and everything like that and they're just like who wants one i was like hey i'm gonna take one see what they got you know what i mean this great yeti cup was in there i don't have a cup for you today because well it's in storage but you know my virtual my virtual i do have these.
Cool uh blue white glasses they help me when uh when i'm staring at the screen for 24 hours i always recommend doing that though get blue light glasses they're pretty cheap too they're all over the place they're like two bucks yeah actually i have the coding in my glasses the blue light coating as well these are actually pretty old glasses hello ballrog wings good to see you i know you join us a lot and uh even we will get we will get some uh dax uh i think we're gonna cover some dax as well here today if i'm not saying dax expression um yeah we can yeah we can.
Yeah speaking here mike the mic is literally you guys can't see it but it's literally just to the left of me so maybe i'm having a little bit of mic issues but let me just turn it all the way up there we go so oh i can hear you really good enough you know what this is the same settings i've had forever and so you know things change though this is a this is a a cloud-based obs so um they're always updating too so maybe they they mess with their audio already yeah um.
Oh yeah and just some quick site uh updates real quick uh we have the shorts still coming out i know joe we talked about this last week too you just finished up a whole bunch of them uh we also have some adobe shorts coming guys and also we just got a whole bunch of pro dev shorts a professional development shorts but those are more kind of like uh time management you know just quick little tips on you know how to you know manage your day better in a minute and stuff like that so we'll have those coming up as well and then also i know we had the multi-tracking and the spanish language thing going strong i think i mentioned this last week too.
That's on hold right now but we will be back with that and we will uh do uh what we did with the excel beginner video we will once we have the spanish versions of those we'll be releasing those as standalone videos and then uh also we have a ton of videos that are gonna be what i call insta drops coming very soon uh you guys will see that coming uh next week we're really ahead of the game right now as far as production goes of what we have ready to put on the channel and everything that i think we're about uh six weeks ahead right now again i'm not going to jump into what we have.
Fully scheduled out because it always changes it always something always comes up but uh with that being said that we're going to get working on some videos that we've meant to release uh before we got to office 2021 uh specifically that's the new excel vba 2019 videos and then we also uh that's those are gonna be taught by trish and she's also producing a 2021 version as well for you guys that's going to be updated just for office 2021 uh things but those uh 2019 vba videos those will.
Come as soon as we have them re as soon as we get them back from the editor and we do our final review we're just going to drop those right onto the channel so you have those to look forward to as well i think i mentioned this last week but that's kind of what's in the works right now we have a holiday week so there's you know we didn't engage in any too much of a heavy project or anything like that and then also guys we do have an instagram we do have a tick tock you can just look us up learn it training we're on there as well uh we're we're also on the twitter and facebook you just look up learn it training or learn it anywhere either one.
Of those and you'll find us there and you'll find our short videos there and a whole bunch of other posts that you might uh like uh but yeah that's what's
Uh that's what's uh upcoming and so without further ado joe let's jump into power bi and the new interface power bi power bi and by the way yeah uh even um yeah i can't wait for the vba too i mean trish is amazing just with every type of thing that she does but vba i think is one of the best languages to really start off with if you're getting into programming language um.You know you know why me and chelsea have done little vba battles here and there but there's just so much more you can do with vba so i just i really do love it um but yeah yeah hi ss how you doing yeah um so uh yeah let's uh get right into it i did have ben i know you gave the file to everyone so if you want to follow along with me during this you're more than welcome to um if you want to re-watch the video later on if you know because we only have 30 minutes so i'm going to go a little faster than normal.
Then yeah of course you know you're more than welcome to but um let's just get into it because power bi the interface changed i want to say about probably like three months ago and it totally threw me off guard and that's why you should consistently just check in on your power bi interface or just power bi the application itself because you know they're they're updating it every month and when i say every month i literally mean every month it's it's it's there's something new every single time i teach it so uh let me share my screen real quick.
Let's get that weird uh effect there we go can you see my screen ben yep sure can awesome so i just want to take a look at the the data we're going to be using that was sent out to all of you it's called payroll 2022 it's fake data of course nothing too crazy here we do have some things like you know the numbering system so if you ever sort you can always reorganize it back to the original entry yeah first name last name employee number division and so on and so forth.
But what i want to do is as always i want to visualize this because we're visual beings we can't just look at this data and say oh yeah we have that many employees in sales oh yeah we have that many hours in sales no we can't just stare at that it'd be cool if we could but that's why we have computers to do that for us we're visual so if we see a nice little bar graph that shows us oh sales has this money research has this much development has that much it's easier for us to you know take that in so that's why visuals like.
This are really important and that's why power bi is such a major tool in the workforce today so the first thing i'm gonna do here is open up my power bi desktop now i do recommend that you all do get power bi desktop it's free you just search it on a microsoft store and download it you do not need to sign up for the web service or anything like that to start using this software although you cannot publish unless you have an account and a license to publish.
So that may be something that you want to look into if that's what you're going to be doing or if you're embedding it on like a sharepoint site but you'll see here this pop-up notification i keep this on every single time the reason being is because right away if you're new to power bi look at this they give you a ton of tutorial videos right not only that but you can keep up to date on what's new every single month in these monthly updates this is really something that i look into because i'm.
Training this constantly as i said literally every month they update the software so just taking a look here you're going to notice i'll click the x on this the visualizations has changed majorly so what do i mean by that well it used to be a little different it used to have the formatting in a different spot so the first thing we're going to do here before we even get into showing you those differences is we're going to load the data in and we're going to use that same old-fashioned etl that i usually talk about when we talk about.
Transforming data so etl the e stands for extraction so the first thing that we're going to have to do is of course extract the data then once we extract the data from whatever data source whether it's excel or sql server access or even salesforce now we can then transform the data so it works for us and we do that in power query and then the last thing we have to do after we transform it is of course.
Loaded into power bi so we can start creating beautiful visuals so that's your etl extraction
Transformation and then loading it back in so that's what we're going to do today the first thing we're going to do is extract the data from excel and let me tell you just like microsoft's old-fashioned uh there's multiple ways to do the same thing for instance i could get data and i can either get it from the excel workbook i can just click on this button excel workbook i can click on this font over.Here get data or i can click on this button over here import from excel so yeah four different ways they all do the same thing eeny meeny miny mo pick whichever one you want right so i don't mind so i'm just gonna click on anyone i'll just click on this one because it's right there and i'll do import data from excel it's gonna pop up the dialog box the file explorer asks you joe what file are you looking at i'm going to choose my payroll 2022 and click open.
It's going to open it up into the navigator and then you'll see here that we do have two sheets in this file we have this canada sheet and if you just want to simply preview something just click on it and i see here well you know that's not the data i wanted so i'll click on payroll and that looks good the only thing is there is a bunch of null values here and we would have to clean up this data but here's a little trick even though you're looking at payroll.
You're not looking at the table itself so you'll see all these nulls sometimes you'll see even no like look at all this there's nothing in column 12 through 15. you could clean that up if you want to in power query but why even do that when if you go down here it says suggested tables if you click on this table one look how beautiful the headers are all nice and place there's no null values so you're more than welcome to either use the sheet where you have to do some.
Transformation or if it's in the table format you could just simply click on table one and just use this data without having to clean it up and spending your time on doing that which is what we're gonna do today to save time but what i always recommend is you still click on transform data it's just a habit you should get into extraction transformation take a look at it see if it's good for use and power bi and then load it into power bi so i never click on load i click transform data all the time.
Now we're in the power query editor same thing right as excel so you'll see ribbons and different tabs and you'll see command groups but our data looks perfect because we chose it as a table so now i can just simply click close and apply which you can find in that home tab and click close and apply once you do that it's going to load it in here you'll see over here in the fields.
Pane once the data is loaded you'll see all the headers of the data that are just holding all the information so if i click into this little carrot here you'll see my benefits will be here my date of hire will be here department division employee number and so on and so forth but where the biggest changes come into play in this interface is the visualizations pane for instance let's say we want to make a visual we want to do i don't know let's do how many employees do we have per.
Division so i think you know which visual i'll use i'll use the clustered column chart so i'll give that a click now i'm going to just close out the filters you don't need to use this right away so i will close the carrot so i get more of a little page there instead of having it halfway eaten up by the filters and you'll see here that it's going to say joe do you want to build your visual and i actually love this this never was like this before it was just your visualizations and you would have to put.
In your information and then they have right next to it joe after you build it do you want to format it i really like the way they set this up in the new interface because if you think about it that's pretty much what we do with anything right like let's say that i was building like a i don't know a dresser the first thing i'm gonna do is get the wood i'm gonna build it and then afterwards what are you going to do you're going to paint it you're going to make it your favorite color whatever you want so same thing what they're saying here.
Is joe build your visual and then after that format your visual and we all know format simply means appearance so it means paint your visual whatever way you want to so i'll go into build my visual and i'm going to take division and just click and drag that into the x axis and i'm going to take employee number and it's going to do a count since it's not a numeric value and the reason i know this is because you'll see this little symbol if it's a numeric value you'll also see a little counter if it's.
A calendar or date value and if it has nothing here then it's just a text value and when you put a text value somewhere it doesn't count instead of a sum right so if i did like hours it would summarize them it would even say this is the sum of the payroll hours but if i take employee number and put it in here it'll be a count of the employee numbers because it's not a numeric value and now i have exactly what i want i've built this beautiful little visual here and it tells me how many.
Employees i have per division and if i just hover i see oh i have 15 employees in new hampshire i have 25 in vermont i have 28 in maine then i also have 30 in connecticut but to be honest we just built this but i don't like the way it looks because right now the way it looks is based off of microsoft's i guess favorite color right or whatever way they decided that this was going to be the default look when you first create something so what i want to do is i want to spice things up i want to make it look the way i want it to look and that's where we go.
Into our next part here which is totally new you never have this all these commands here were down here at one point they were over here if you remember some of you might even still have this version where it is down here but this is the new interface so when you click into format your visual they actually moved a lot of stuff around it's not all in one place anymore there's two things there's called visual in general now the general is going to have things like properties which i don't use that often because the properties is the size.
Which i could just use the sizing brackets to do that and then the position is literally the horizontal and vertical position on the page once again i could just move it around to do that if you wanted like specific specifications and details you could use these but i typically don't title is on here as well i don't like the way the title looks um it says like count of emp number some people might not know what that means so i'm just going to change that and say employees.
Per division i think that looks good i also do want to change the font you can change the font to whatever i'm going to use ariel black i'm going to make it a little bigger i'll do 16 i'll bold it i'll underline it i'll change the color of it to i don't know let's do red and then i will align it in the middle because i that's just the way i am by the way if you're following along you can do whatever you like you don't have to follow me step by step pick what your.
Favorite color pick if you want to bold it pick a certain font once i'm done i always tend to do this i close the carrot that's just a habit that i've you know created because if you open up all of them you're gonna have so much to scroll through so every time i do something i then close it and i move on to the next like effects what do i want an effects do i want to color the background oh look at that do i want a visual border i do i'm going to turn that on and i'm.
Actually going to round the corners i don't know i think that looks good now besides just the general right where we have some of those uh basic i would say formatting we're going to want to format everything else so for instance maybe i want to format new hampshire vermont maine connecticut i might want to format the column colors right maybe instead of having to hover over to see that 25 i want the 25 in the middle.
Of the column right that's stuff we can do and that's where we're going to be in our visual section here now if you don't know what something is what my recommendation is is turn it on and off like if i'm looking i'm like what does x-axis mean what does that do if i turn it off oh okay so the name of the divisions disappear if i turn it on oh they come back okay so i know what i'm affecting now so if i want to change the color of these it's going to be here.
And then i can make them a little bigger i can change the color to red and i think that looks good then i close it out if you ever see this little uh sign it's just telling you joe you know we're trying to fit everything i know you said you wanted 12 font but we can't because you didn't make the visual bigger just go like this and then it'll disappear or you can squeeze it whatever works.
And then you can make them as big as you like so that's all it's telling you it's just saying hey joe the font was reduced because the size of the visual just isn't matching so don't worry about that that's not really affecting much now i'm going to click on the y-axis what does that do i'll click it on i'll click it off oh okay it's affecting the numbers so i know for you know a fact that that's going to be affecting 30 20 10. so i'm going to click the drop down and i can even change the range maybe i don't want to.
End at 30 maybe i want to end that 35 or maybe i want to end at 40 runs out of 40 on there and whatever you like i'm just going to auto it though i think that looks good the values themselves what do i want to do maybe i want to make them a little bigger maybe i want to change the color to red and look at that everything is just working i really don't like the title it says count of emp again so i will change that title to um employees count or something employee.
Count that looks good right let's turn our heads a little bit employee account looks great and then that's it i think that looks good i may also want to just change i know that this is and these are minor details but i'm very when i'm in power bi i very nitpicky so for instance this is all um looking great this is all capitalized i don't like that so i do want to just go back to the title here on the x-axis and just type in divisions and there we go let's say you don't have.
To do those things but you'll you'll have your own flow around those things uh for the grid lines i could barely see them they are dotted i'm gonna go to gridlines and i'll change that color to black let's see what it looks like i love it um if you are like you know joe i don't like the dots use dashed you can use dash you can use solid lines whatever makes sense for you you can change the width of those lines make them a little deeper.
I think that looks good for the zoom slider what is that turn it on try it out who knows what it is ready turn it on oh okay so it makes a little slider here and you can zoom in on the data if you're like i don't really need that turn it off columns let's take a look at the columns right now it's showing all the colors now this is where it's going to be a little different so it is showing the default for all the colors so if you change something it changes it for.
Everything but you can show all turn that on and then you'll get each individual one so i can say connecticut could stay blue let's do a green color if you don't see it here just go to more colors and you can choose a lighter green i think that looks good i'm gonna do a red and i'm keeping it kind of like a light light color for everything let's do a light yellow perfect and now we're really just making things.
Happen here uh data labels i said i wanted the middle right here i wanted instead of hovering i wanted to say 28 in the middle so i will turn on data labels and then i see that they're turned on but i don't like where they are they're at the top so i'll change the position to the inside center if you're like yeah i can't really see that change the color of the values there you go and now what i'm seeing here is a beautiful little chart.
And we can move this chart over here i think that looks great we can create another chart make sure you click away because if you're clicked on a chart right i know that i'm selected on this because of the sizing brackets if you click on like let's say a gauge you're gonna be like oops i lost my stuff don't be nervous just simply go back to what it was it was the clustered column chart it keeps all your formatting click away from the visual and then click the next visual you want.
To use and let's say that we want to just create a quick little hours in the values and i'll do a little hours thing once again we build the visual and then right next door we modify it we format it we paint it to the way we want it to look and i just don't like the way it does like 7.02 or 0.00 k or 3.51 k i like regular numbers so i go over here i also don't like how it's at 7 000. i want it to be like 40.
4200 so that this comes over a little more so i'll do maximum for 200 that looks good it'll never get to 7000 that's why i did that it's probably max out at 42. colors maybe i want the fill color to be a little different i could choose whatever i want maybe a nice purple that looks good and then for my data labels if you're wondering what are those remember just turn them off have some fun with this turn it off turn it on okay so i see it's affecting the zero.