Of topics I’ll be covering and the links are in the description below, so you can jump to a specific topic anytime you want. I’d recommend watching this whole video first (you can change the speed up and down if you like). Then come back and follow along step-by-step using the “Download Files”. This Tutorial is complete in itself, but at times I may mention more advanced material which you can watch next. All the links, including the one to Download files, are in the Video and in the Description. This is a slightly fast-paced video, packed with a lot of good stuff.If you’d prefer a.
Slower-Paced Tutorial, I’d link to that as well. Or if you feel you already know this stuff: then check out my advanced videos on Modeling and DAX. Now if you get stuck anywhere in this Tutorial and have any questions, you can ask me directly on my LIVE TalkPowerBI show every single Friday. Just make sure to subscribe and click that bell so you are notified whenever I go live to answer your Power BI questions just one tip for the best quality playback throughout this video change the auto quality setting instead select one of the HD options. When you hear Power BI, I want you to think two.
Things. •Power BI Desktop• PowerBI.com. We’re going to talk about PowerBI.com in the end. But we’d start with Power BI Desktop . Because it's one tool that you must master to get started and eventually become a Power BI Pro. Power BI Desktop is the authoring tool, used to create Power BI Models and Reports. There are three phases to developing a Power BI Project.
Author •Publish •Consume The most critical step is “Author” and you do that using Power BI Desktop. Let’s take a look. sure you have bar bi desktop installed and for that you just Google bar bi desktop download and go from there if you need more help then we're gonna link to a video which gives you more detail and tells you about the different options so once you have power bi desktop and you launch it for the very first time I remember when I did it it was scary I was staring at this big white screen and I wasn't quite sure what I needed to do well this time I'm here to help you so why don't tell you is that power bi think of power bi as a.
The ability to connect to lots and lots of different data even if the one that you you were trying to connect to is not listed here which is pretty rare but still you might be able to connect to it using either ODBC or old data feeds or some generic connector like that so pretty much doesn't matter what your data source is you would be able to connect to that again you can see a long list of sources and again we're gonna we're gonna see some example of that but you can connect to more than one data sources and bring it all together into one single model so you can be selecting maybe one file from.
Your sequel server and then one table from your sequel server and then something coming from excel something coming from a SharePoint Online list and on and on and on and all of that you can combine it into this one spot so we're going to start with our data source and we have an excel file in this example so I'm just gonna can select that and click connect now based on the data connector you you choose it's gonna ask you different questions for example sequel is gonna ask you to look in the name of the server and credentials and so forth for excel all we need to do is to point it to that specific file and this file is part of your download so go ahead and use that file so I specified bar bi that.
Hey this is the file to connect and once you specify the data source what it's doing is its examining that data source whatever it is Excel sequel access or something else and it's checking what is available inside of that and that's what it comes back and shows that to you in the navigator not Excel sometimes this can be confusing because it might return what look would look like hey it's showing me the same thing twice and notice here this has this date date key and if you go here these look similar because in Excel it shows you the sheets and if it finds tables Excel tables inside it shows them as separate as well.
You can tell that by the I can it's using and the fact that tables show up as the table name so in this case we have these clean tables now if you do have a choice like this I would always select the table just because tables have more crisp boundaries whereas sheets sometimes you know how you can mess up the boundary of that so in this case I want you to just simply select the top tables up here and you can see that as you click on something it shows you a preview of that so if your data source has lots and lots of tables there's gonna be a quick way to kind of just glance and check to make sure that you're getting the right thing so we're gonna select all of this and we are going to click load now you see what.
It's doing right now is it's actually connecting to your data source whatever you would supply in this case it's Excel but could be sequel and what it did is it made a copy of that data set so nothing changed on this screen but let's talk about this layout we zeroed in on this get get data so you might see panels here and we'll talk more about that but already on this fields panel I didn't expand it earlier but earlier this would have been completely blank and now you can see these stables are being shown here what collapses for now and then go over to the left side where we have these three panes there is a report data and model or relationship view so report is.
Posts Related:
- Excel Today - Setting Up Teams
- Excel Today - Educon 2022
- How to save Microsoft Forms Responses & Attachments to SharePoint Lists or Libraries & Send Email
Still blank and that's okay but if you switch to the data tab you can actually see the underlying table that it has fetched from that data source and you
Can click through and kind of examine these tables and see how the data has come through what I'm gonna do is save this file and then I'll check back in with you all right so here we have this is the original data source that I had pulled into my power bi file and this is the power bi that I had saved now you can see that the power bi file is is much smaller in size than our original data source which is pretty amazing given that Excel itself it stores the.Data in a compressed format but all I want to show you here was that power bi is really really amazing at compressing data and that's how it can not only handle millions of rows but hundreds of millions of rows and beyond I'm gonna show you the third view which is the model or relationship view which is right there and if you click on it you will see that you see the tables here as well we'll come back and talk a whole lot more about relationships so far we have brought in a very extremely clean data and in real life that's rarely the case well okay that's actually never the case real is always messy data and you know here is one example of that now the odd thing.
About this is that you or you know you me may not call it messy what I call this is that often we would find datasets which are human friendly but they're not machine friendly as far as machines are concerned this data is noisy I mean these colors don't make much sense machines don't care about that when I say machine I really mean Power BI. Power BI doesn't care about these header rows again that's been there just to assist us humans and and what is that we have this data spread out on columns that's awkward for power bi it's redundant because the grand total is is essentially repeated it can be calculated from these monthly totals it.
Has subtotals and a lot of other things going on here so again this is human friendly but not machine friendly to power beyond this is messy data let's see how we can clean it up and bring it into power bi so we're gonna go back again to or get a data and click excel from this drop-down and this time we're gonna select our messy file the budget file which again is part of your downloads as well now it does the same thing it examines the file and checks what's inside in this case is just one sheet so it's we're gonna select that but this time we're not gonna click load because we're gonna enter a magical realm my friend the kitchen of Power BI are you ready for that so when you click Edit watch what's gonna happen it's actually gonna pop open a new window so.
I'm gonna you know make this window a little bit smaller as soon as it gives me a chance so there you go so you can see how the Power BI window is in the back but now this new query editor window has been open and you should think of this as a component of power bi but an extremely powerful component of power bi in fact power bi has two engines that you need to master to become a pro one is the query editor it all starts here all data goes through this this is the kitchen of power bi and the other engine is the model where you.
Need to understand relationships and Dax we'll get to that stay tuned for that so here we are in the kitchen of power bi and remember when I said every data comes through here so you notice here that even when we clicked load we did not click Edit for these queries they still are placed here but there's not much cooking or cleaning going on for this it's it's simply just connected to the data source and just brought it back in but for budget we're gonna sharpen an eyes and get at it but before we dive in let me orient you to what's going on here now for first of all what do you have in a good kitchen you have lots of gadgets and appliances and this is.
Chock-full of that so you've got lots and lots of goodies up at the top in the ribbon a very familiar interface with your work with office Excel PowerPoint on any of these tools and it's gonna be so much fun just selecting our tools here and getting at our data and cleaning it up so that's at the top the ribbon on the left side here of course you're seeing the queries now you can organize them in groups and folders as well I'll let you explore that on your own that's usually needed only when you have lots and lots of queries let's say you end up with 40 and 50 tables going through a to power bi on the right side.
Here we have the query settings pane and it has a name which is really important because this is not just the name and the query editor this is the name it's gonna end up with in your model as well that's what's gonna show up so you want to give it a good name if it doesn't come if it comes with a quirky name or something like that and applied steps the part you see over here this is where all the magic happens now Barnea does try to help us out a little bit here so let's say let's get started and cleaning up this data the first thing I want you to do is go over to the applied steps and just delete this change type and promoted headers so Barbie again was trying to help us.
Trying to make the best guess of what it needs to do with the data but it didn't get it right that time and that's okay we'll excuse it for that all right so now we're gonna get to work now you see the first thing I notice over here are these header rows header rows which are again were are there for humans machines don't eat them this is just you know FII and we would rather focus on the data which is the budget amount so we need to remove these rows and again what I talked about the kitchen of power bi you have all the tensions and in hinds and everything available to you over here so the the one looking for here is this remove rows so I want you to go ahead and click on that and from here select the remove top rows option that's gonna pop up a dialog box.
And ask you how many rows and in in our case we want to remove the first three
So I'm just gonna type in 3 and hit OK now here's where I want you to watch really carefully because if you blink you might miss it so I'm gonna hit OK and watch what happens so first thing that happened is those three rows were actually removed so in the middle it's showing us a preview of the data as it's working on right now so those rows are gone but well you remember I said this is where the magic happens watch what happened here it add a step here I'll come back and talk more about this but yeah keep your eyes you know glued to.This section watch what watch what it does so remove the top rows and now what do we need to do oh gosh look at that I mean I don't want it to be called column 1 column 2 I have my headers right here I wish there was a button which would make the first row as headers we Scranton my friends there it is used for asteroid has headers so go ahead and click that you see how easy it is it's just you know I love doing this so you use first or head as headers and again you notice the change in the preview pane but more importantly you also see it as recorded here so promoted headers and again sometimes it tries to help you and adds steps on its own you may or may not need that we leave that in for now.
All right so we promoting headers now the next thing we need to do is notice here then there are these subtotal rows again they're redundant as far as power bi is concerned so we don't need that to filter these out I want you to click on this this triangles filter icon next to the column name once you click on that it's gonna show you a lot of options now sometimes these options are actually well these options may depend on the type of the data field so if it's a numeric field you would see instead of text filters you would see actually let me just show it to you really quick so it'll say hey number of filters and then you can say greater than less than and so forth but let's come back over here.
And here we're gonna say text filters does not contain the word total now watch out though because power bi the very editor piece is case-sensitive so make sure the t is capital and you type it just like that and again we're gonna hit okay and watch the magic happens so again it remove those rows but more importantly it added and that's step here if you are an excel user this may.
Seem familiar this may feel like Oh macro recording isn't it well it is but way more awesome my friends for one for me it feels like a time machine I can go step by step and examine exactly how things were how my data looked like at that step now what this does is it does two things one it makes your queries in this process of cleaning shaping and transforming your data self-documenting how many times have you looked at somebody else's report and weren't able.
To figure out how they exactly did it well how many times does that happen for your own reports where you open it after a month or even a week and say scratch your head and say how did I do that I don't quite remember well that is all a thing of the past because again you can time travel and you can go step by step it's all documented as part of you doing it and not just that this is way more awesome than macros because in macros recording it and editing it is a whole different level editing you got to dive into the code and look at that and hear editing or making changes to an existing query.
Going back and fixing things let's say your business data changed and now you got not three header rows before header rows it's really easy to change the step all you do is click on this gear icon and you can change the exact setting that was for that step you can delete a step just by hitting that red Red Cross there you can insert a step at any point if you want you can just click on the button again it's gonna say hey do you want to insert a step and go ahead and insert it you see how easy it is in power bi our job isn't done here so let's keep going so I'm gonna skip ahead to the last step that we had and we just have a few things left here for one if you scroll all the way to the side then you're gonna notice that there is the grand total column here which again is.
Redundant you don't need that we can calculate the grand totals by summing up all of these values so we're gonna right-click on that and say remove you can also find the remove columns button right here on the toolbar now hey we're on to my favorite steps now first of all this data shape why is that clumsy for power bi again it makes the table very easy to read for humans let's go back to that so why do we humans use this format well because you know we can easily scan across and say oh well how much do we sell in fenders in May and.
And you know we can it helps us but for machines it's clumsy because imagine if you go to power bi and ask power bi to say show me the total sales for the year well now it's got a sum up not one two three four but twelve columns and now imagine this data if it was spread across not just one year but multiple years you see how the problem just compounds itself just gets more and more complicated what we would rather have is instead of this data being spread out in columns you would rather have it in rows now this is something which was I found it so hard in the old world the BP era the before power bi oh my god I don't.
Want to go back to that and it was incredibly hard to do for me and I did know a trick or two and sequel and pivoting to stop it was never easy I never looked forward to that but of course in power bi let me show you how easy it is so what we're gonna do is we're gonna select the columns that we need to move two rows and that that transformation by the way it's called on bit and guess what yes there is a button for that yep we're in the kitchen of power bi and we have all the gadgets and instruments available to us so we're gonna go in here and click on this unfavorite columns button so I mean let me do that and again watch what happens in the table and on the apply steps I'm.
Gonna click on that and wallah look at that so all my data has been moved from columns to rows and of course that step has been recorded in in the applied steps let's do a few more steps here so one what we're gonna do is we're just gonna rename this column and what I did was double click I guess you can right-click and rename as well there we go and we're just gonna rename that to month and we're gonna rename this one to budget them out and next I'm gonna change the data type off the month column to date and for this you can go up in the in the ribbon here and change the data type from there what I find myself normally doing is I would just click on this this icon and next to the.
Column name and then select the data type so and the one you want to select for this is date great now that that's done we are ready to move out of the query editor and and remember this was a window that opened outside and the way to do that is just to hit close and apply as before it's going to the budget table and boy this was a short table so it made quick work of that and now if you notice on the field side you would see the new budget table you would see it in the data view you can examine how the data came in so that's how a budget table looks like and it also shows up in the relationship view right there.
Once you have all of your data sources connected like that and again you can connect to lots and lots of different data sources and for each data source and data set and table that you bring in you can apply exactly the clean shape and transform steps that you need in a query editor but the best part is from that point on to refresh all of that data from multiple sources with a lot of cleanup steps all you have to do is just click this one button refresh and it's going to go connect to all the data sources apply the steps that you just recorded and pull the data in now if you get tired of clicking this button you.
Can also automate the data refresh in power bi we're going to talk about that later let's talk about the next exciting concept in power bi which is the magic of relationships now for this what I'm going to do is I'm going to delete these lines these relationships and these lines represent the relationships which have been created by power bi by default and rearrange the tables as well and I'm just going to right click and delete this so let me do that rearrange the tables and I'll be right back I am back and our tables are nice and pretty now when I work with my students and my clients I place a lot of emphasis on how.
These tables are arranged and I always arrange them in a very specific pattern now this might seem silly putting so much emphasis on this but this is important because this underlies really important concept now my friends here is a secret to really becoming good at power bi is you build models not reports most people in power bi all they care about is the visualization and that's the end goal and they have that in mind like oh I'm gonna build this report and needs to show sales by year or whatever I whatever report is building on but every power bi report should be underpinned by a really strong robust.
Model and for that the this that's why this stuff is important so the way I've arranged these tables is who is with the data tables at the bottom and the look-up tables at the top now we're gonna have link to a separate video which with more details about data and look-up tables and their differences and also about power bi modeling best practices but for now I'm gonna I'll give you a quick version data tables record transactions and they have lots and lots of rows and they're really tall so sales can have well this one is short it's sampled data but this can have millions and millions of rows so that is our data tables tall and it's intentional that I have made this tall now our lookup tables are the who what where when how so if you look at sales.
Well who bought it what did they buy the product where did I buy territory when did I buy calendar and sometimes you capture additional attributes in the house so these are the look-up tables and they're usually not as tall not as big if you had a hundred million sales transactions well hopefully you didn't sell it to a hundred million different customers or you didn't sell a hundred million different products it's just not gonna happen in a real data set so maybe you would have a few hundred or a thousand or maybe you know so less number of customers so these are our data tables and these are or look-up tables now traditionally if you are you know are an excel user what you would do.
Next is you would do a vlookup from here to there so you would say vlookup the product key to the product table and just shall bring in everything down and essentially flatten the table create one big flat table now you don't need to do that all you need to do is drag and drop that's how easy it is to can create the relationship between these tables so I'm gonna expand this just a little bit so I can see so I'm gonna take product key and Here I am kind of just dragging it and I'm gonna drop it on the product key over here and again the direction doesn't matter and that creates this line which represents the relationship and if you hover over that it's gonna highlight the columns that it's that are connected I'm gonna do the same with the.
Customer key so again this time again the direction doesn't matter it automatically detects which one is the data which one is a lookup table and if you if your tables are set up correctly well in this data set they are then the way it would look like is it'll have this one on here and star on this side now a little bit of technical speak this is indicating a one-to-many relationship what that means is that the customer key in the lookup table is unique it only appears once and whereas in sales a customer key can appear multiple times because hey the same customer can come in and buy multiple times and the other thing I'm gonna point out is the directional arrow on this relationship.
Now this this again if you did things right this should point down to the data table from the lookup down to the data table and generally your relationships when you're starting out should look like this now there are other patterns there are many to many relationships they're bi-directional relationship and we can link to a video which covers all the details of that but when you're starting out your relationships should typically look like this until you understand when you use or those other types you should not use them let's go.
Ahead and connect our other tables using the keys so I'm going to drag the sales territory key over here and for the calendar table I'm gonna take the order date and connect that to the date column in the calendar table nice now that's done let's also give some love to our budget table and we're gonna do the same thing I'm gonna connect the product key to the product key in the product table and I'm gonna use the month budget data as a monthly level to connect to the calendar date just a quick note here on the calendar table that is one of the most powerful tables in power bi and almost well every data set should have it now this one we're using a simpler version but I am going to link to the ultimate calendar table which is a lot more powerful and you can.
Watch those videos next and learn all about that now that our relationships are in place let's go and have some fun with our data so for this we're gonna switch back to our reporting panel over here and we're not really trying to build a rapport we're just simply trying to have some fun and just explore the data now here if you have the visualization pane not expanded then make sure you expand that and here you're gonna find a chock-full of basic visualizations now you do have the option to add custom visualizations as well you're gonna cover that in a separate video but there's a lot of really cool graphics and visualization available to you we're gonna start with.
The one that's called the card element which is right there so you click on that and it places it on your report page over here so and and then now you can add fields to it so again here in under that now once I have the selected I have these three panes here let's go through them so there's the fields there's the format and then I think the last one is analytics so first one feels the card one only takes one value so I'm just gonna drag and drop the sales amount field over you're perfect so we can see the sales amount in fact you know what card is not that much fun so I'm going to switch that to a table so.
That's right there I'm gonna click on that and now it's a table now I notice that the sales amount isn't quite formatted correctly I would rather have that be shown as dollars in fact but even before that what I'm gonna do is switch to the format pane search for size and just make that a tad bit bigger so let's go with maybe 12 font perfect so it's a little bit bigger and again I want to format it as dollar so I'm gonna go to the modeling tab and say I want this to be currency and I'm gonna change the decimal places to zero all right so that's taken care of yep so you can see sales them out here and and and again this is the power of relationships now I can go to my.
Calendar table and slice and dice at any which way I want let's start by slicing it by ear now sometimes power bi does this well which would seem silly to you but hey he's just trying to help you the best it thinks it can but sometimes it'll do something like where it's you can notice here that it's actually doing a sum of the calendar year which of course it's silly for us but you can easily fix that by clicking on the drop down over here and just saying it don't summarize so instead of sum you're gonna say don't summarize and now we see that we have a nice stable off or a salesmat so what I'm gonna do is I'm gonna copy pasted that table so let's just go here copy and paste so I have a copy of that here because what I want to do is I want to.
Instead of sales amount I want to show budget amount so I'm gonna hit X and remove that column and go to my budget table and find the budget amount from there cool so I'm gonna quickly reformat it as well as we had done the other field perfect now you can see that we only have budget for one year so why don't we add a filter adjust for that year so we can dive in deeper in that year so for this I want you to expand the filter pane over here and you would notice two sections in here one is filters to apply just to this page or.
Filters on all pages and eventually you are gonna end up with adding more report pages as and when you need to build additional reports but for now we just have one page so what we're gonna do is we got a drag or here over to this filters on this page and then we'll change this to basic filtering you could do this in advanced filtering as well but basic filtering and we will select the year 2016 and now that we have that the next thing I want to do is I just want to see it by month rather than year so for that let's go over to this table.
I'm just trying to move them apart great and I'm gonna move in the month right between year and sale so I'm going to drop that here but watch what happens so notice the sort order is a little weird and you can see the sort order it's sorting by sales amount we do want to sort by month but if you try to do that notice how it's sorting alphabetically because that is the default sort order thing up for text columns at least you can change that the hope we don't want or month sorted alphabetically and you can do that by selecting the month column in from your field list and then go to the modeling tab and then select.
Sort by column drop-down and you can tell it to sort it by a different field and the field that we are going to use is the month number that way January has a month number one so it becomes the top of the list and once you have made that selection you will notice that it's now sorted correctly let's do the same for our budget table and this time once you have done the sort by column you don't have to do it again notice that it's already showing in the correct order so you can see the power of relationships here where they essentially work like.
