Create Your First Dashboard Now in Power BI
Welcome to Power BI Tutorial Blog. Now, power BI can feel overwhelming because it has so many different components. That’s why, what I suggest you do is that you just follow along with me. I have the files that you need in the description of this video, download them follow along. And after 20 minutes, you’ve created first power BI dashboard, which you’ve published to the web. Ready? Let’s get moving in case you don’t have power BI installed yet you can do that really quickly from the Microsoft store. So just open Microsoft store from your PC. Look for power BI. You’re gonna see it pop up here, power BI desktop, select that and install it from here. So you’ll see the install option. If it’s not installed yet on your PC, you can also just go to power bi.com, go to products and install it from there. Once you have it installed, just open it, log in with your work account. And then you’re gonna come to, to this first page to create some reports. You are gonna want to get some data. So that’s the first option we see here. You’re gonna see your recent sources down here and access to different tutorials on power BI. If you have time, make sure you check these out as well. Now I’m just gonna close this and introduce you quickly to the I here, but just so that we can zoom in, I’m gonna make my screen smaller. So the first place we land on is the report tab right here. But first we need to add data to create any reports. It’s asking us whether we wanna import it from Excel, SQL, and some other common data sources. If you don’t find your data source here, you can can get data from another source. And it’s gonna bring up this popup where you get a list of all common data sources, and you can see you have a lot of options. You also have these in categories so we can select file. We see Excel workbook, text CSV, and so on under other, you can import data from the web, share point, ODATA feed and lots of different options. In this case, my data is in Excel and it’s also in a text CSV. So we’re gonna be importing two data sets. One is our invoice file, which is a text file. And the other one is our customer master data. That one comes in as Excel file. I’m gonna start off with the invoice data. So I’m gonna click on text, CSV and connect. You can browse for your file here, mine right here on the desktop. Let’s open it. This is gonna open up a preview of your data. Now you can decide if you directly wanna load it to power BI, or if you wanna form this. Now, in most cases, you’re gonna want to transform this because you wanna make sure that your data is recognized correctly by power BI. So for example, your numbers are properly recognized as numbers. Your dates are proper dates, and sometimes you might want to clean up the data or add new columns. You can do that. If you go to transform data. Now this view here is the power query editor. And if you are using Excel and you’re using power query there, this is the same power query that you have in Excel. Notice the headers are automatically recognized properly as headers. They’re not inside our data, but they got promoted. So power query automatically applied the steps. Because this is how the data looked first. Then it automatically promoted the headers and it changed the data types. So it tried to recognize whether we are dealing with a number here. We can see that with this. I con this is a whole number, or we are dealing with text. It’s good practice to go through these, to make sure that your values have the proper data types. This is going to minimize errors. Once you start your reporting. Now, in this case, it’s fine. I have sales as a whole number while I’ll just update that to the see format. So it looks like I have no decimals here, but maybe in the future, my sales values will have sense in it. I don’t want them to be cut off. So I’m gonna change this to the currency data type. Another thing I wanna do is to combine these three columns together because currently I have years separately, month and day separately, I get a date column. Having a date column is a necessity. If you want to do any type of time intelligence analysis in power BI. So it’s super easy to do that here. You just have to select these three columns. I’m gonna select month first because my regional settings are us regional settings, then day and then year. Right mouse click and merge columns. For the separator I’ll go with slash, call this date and click on. Now I’m not done here because the date has a text data type. That’s not correct. I’m gonna adjust this to the date data type. Now notice all of my steps are, are registered here. So every time I upload a new file with the latest data, all these steps are automatically gonna be applied to that data set. So that’s it for the invoice data file. Now let’s also upload the customer master data. So you can just right mouse click here and add a new query to this. Or you can make your selection from the ribbon up here. My master data is an Excel workbook and it’s right here, customer master, click and open again. We’re gonna see a preview of what’s inside the file. Now inside the file, I have a table. You can see that with this icon, and this is the sheet. So if you click on these, you get to see the review on this side. Now it’s always best practice to go with the table. If you have tables in your file, this way you can avoid numbers that might just show up on this sheet that you don’t want imported, then click on, let’s check what we get. So we have customer ID. That’s a whole number. This is text. Customer name. We have a lot of information about the customer here and we have city province. So it would be great to split city and province into two separate columns. Well, you can do that easily with power query. Right mouse click, split column, and split column by because if you take a closer look, the logic is that the province is inside the brackets. So we’re gonna go with, by now. It already picked it up. I’m just gonna add a space before that and then decide whether I want it at each occurrence of the, or the left most steel. Now, in this case, I just have one bracket here. So it doesn’t really matter. I’m just gonna go with. And I get the city in the first column and the province in the second column, just gonna double click here and rename this to city. Now the province has that extra ending bracket. We wanna remove that. So let’s select the column, right mouse, click and replace value. So notice with the right mouse click, you get a lot of common options that you’re gonna need when it comes to transforming your data. You can also get to these options from the menu up here, the value to find is the closing bracket. We’re gonna replace it with nothing and click on. And this now is our province. I’m also gonna rename the query name to just master customer and presenter. What I also wanna do is remove columns that I don’t need in my detail model. Now you can just highlight a column and press delete if you don’t need it. But since I have a lot of columns here, don’t wanna scroll back and forth. I’m gonna go to choose columns and choose the ones that I actually wanna keep. So I definitely wanna keep customer ID because this is what connects this table to my sales table. The ones I don’t need is this one, the phone number, fax number, website, and delivery method. So I’m gonna uncheck these, click on and I can see a removed other column step was added to this. Now all of these steps are recorded. Every time a new master data is uploaded here. These steps are automatically gonna be applied. If you did something wrong, you wanna remove a step, just click on the X here. Or if you wanna insert a step, you can just go back to a previous step and do what you want on this data set. And it’s gonna insert a step in between. So now that we have our files uploaded here, let’s go and apply this so close and apply. And it’s gonna load the data into power BI, into the data model. So now that the data’s there, we are ready to build the visuals, but not so fast. Let’s check where our data actually went. So we can see our fields here. All the columns are organized in order. This is our invoice data, and this is our master customer data. The connection between the two here is the customer code and customer ID. So instead of merging the tables to bring over all the other customer information that we need to the invoice data file, we are going to use relationships and connect these together, right? So that’s the better approach. That’s why we have relationships down here and notice that that relationship was automatically set up for me. How did it know what to do? Well, power BI is smart enough and it recognized that customer ID and customer code seemed to be the same thing. This is the look up table, and this is, is the fact table. And it automatically set up that connection for me. And I did that because I have a setting enabled in my options that does that automatically. It’s under file options and settings under data load. I have a check mark for auto detect new relationships. After data is loaded. In case you don’t have that, you are gonna want to create your own relationship manually. From this view, all you have to do is click on the one side and drag with your mouse to the other side. And it’s automatically gonna insert a relationship for you. Now, what if you wanna get back to your data? What if you forgot to add a step or you need to split some other columns? You’re gonna see your data here in the data tab. If you ever wanna go back to power query one way of doing that, just write mask, click here and edit your query. You’re gonna come back. You can insert new steps or update anything that you might have forgotten. So now that we have our data loaded and we have our relationship set up, let’s go and start to create some reports. Creating visuals is really easy in power BI. You just have to select what you want. So let’s say as a first step, I wanna analyze sales by this one right here, which is customer category name. So I’m just gonna place a check. Mark beside these and power BI are automatically inserted a column chart. If I don’t want a column chart, I can just adjust that selection from the visualizations right here. So I want to get a clustered bar chart instead, and that’s it. I don’t have to do anything special, but just click on what I want. Now. This is summarizing the data for all the years. I don’t want that. I wanna add a filter for year. I wanna add that as a slicer. So if you just take a closer look at the visuals to see what they are and what options you have, you’re gonna come across this one. That’s called slicer. Now, if I click on it right now, it’s gonna change that visual that I just made to the visual that I select, because it’s gonna think that you wanna adjust this. So make sure before you make a selection for something new, you click away. Now, I’m just gonna press control + Z to go back. I’m gonna click to the side here and insert a slicer. So this time I started with the visual first. Now I need to decide what do I want to have on the slicer? So here you have some options that you can adjust. Well, I wanna add date, but notice there is this arrow on the side, meaning that I have more options. So power BI went ahead and automatically added a date hierarchy to my single date column, because I wanna add a slicer just for a year. I’m gonna select that and it’s automatically gonna add it here. Now, this is one way of looking at dates to notice. Whenever I’m updating this, this visual gets updated as well. I’m not a fan of this view. We can change that. Click on the options right here. You can decide if you wanna have a list like this, or if you wanna have a dropdown. So I’m gonna go with a dropdown, make this smaller, drag it and place it somewhere on top. Now, when I click on this dropdown, I can select which year I want and my here adjusts automatically. Now, if you wanna change the look of this, you can do that with these options as well. So if you take a closer look, this is the formatting option. Here you can decide if you want to show or hide the axis. If you wanna add data labels, I’m gonna turn this one off. You have options for color position, and so on. You can adjust the title. I’ll just call this sales by category again, adjust the alignment size and so on. So as you can see, you have lots of options here and automatically you also get this tool tip. So when you hover over each of, of these columns, you get a better glimpse of the data. Now I just wanna hide these access labels as well. So let’s go back to Y axis and turn off the title for this one, and also turn off the title for the X axis. So this looks much cleaner. Now, if you wanna change the color of this again, you can go back to the options under data colors. You can adjust the color directly from here. Next up. Let’s add a table that shows our sales value by month and does a year on year percentage change calculation, and also a year to date calculation. So we’re gonna do time intelligence analysis without writing a single function. Check this out. I’m just gonna click to the side here and go and grab this table visual. Let’s drag it up here. I wanna get month in there. So just place a check mark, and then I wanna have sales value. So that shows me the sales value for each month. I want to get the comparison to previous year. If you wanna do this without writing the function from scratch, you can. You just go here and click on quick measure here for calculations, scroll down to time intelligence and you, you have some options. I wanna get a year over year change. Now I have to select my base value here. It’s sales and a field for date. My date field is right here. I’m gonna click that and drag it in this box. If you get an error at this stage that speaks your date column, doesn’t have that calendar icon in front of it. This calendar icon has automatically created a calendar table for you behind the scenes. That’s why we automatically got these additional breakdowns. Now it’s good practice to add a separate calendar table yourself, to the data model, especially if you’re dealing with different fact tables. And each of those fact tables has its own date columns, right? So you wanna have a separate calendar table in your model, but if you’re just getting started with power BI and you wanna test things out, or you have a simple model, you can use this option. So I’m gonna click on. And formula got automatically added by power BI. You don’t even have to look at this. You can ignore this. You can of course check your numbers to see that they are giving the right result. So with this selected, I’m gonna place a check mark here, and I’m gonna get my year over year percentage calculation. Now, if you don’t have that calendar icon, how do you get it? Well, I have that option ticked here. So you have to go to options under data load. You have this auto date time for new files and here for the current file. It’s also checkmark. So auto date time for time intelligence, right? So if you wanna get that, you have to place a check mark here to at that calendar icon. Now a lot of advanced power BI users have that unchecked because they create their own calendar tables. So just remember that option is there just check market or uncheck it depending on your requirements. Now, next up let’s also get sales year to date. So this time I’m gonna add a quick measure in a different way. I’m gonna go to my base measure here. Right mouse click and add a new quick measure. Scroll down to time intelligence and select year to date total, because I made my selection from here. It automatically put the sales field as my base value. Now, all I have to do is the find my date column. So I’m just gonna click and drag it here and then click on. And add this to my table as well. So this part is done next up. Let’s visualize our sales by province. I’m just gonna click to decide here, click on this map chart icon, add sales as my KPI and province from the customer master data. And you can see immediately that these ones got highlighted. So I can scroll with my mouse hover over any of these states. I get to see the state name and the sales that’s associated with that. Now the great thing about using filters in power BI is that it’s not just a slicer, just acting as a filter. But if I click for example, on supermarket here, I can see my data in my table, as well as my map chart is getting sliced as well. So this way I can quickly see which states are in these super market category to remove the filter. You can just double click on the white area here and it’s removed. Now. That’s not just for this visual here. You can do that for any visual. If I click on April, everything is filtered to April. If I click on a specific state here, everything is filtered down as well for our last visual. Let’s add a line chart for quantity by month, select line chart, place a check mark beside quantity and a check mark beside month. And that’s it. We are done. You can add a header to this report by inserting a text box type in your text, adjust the format thing as you need and place your text box. On top of your report, you also have KPI cards that you can add to your report. So let’s say one KPI that we wanna track and we wanna see is quantity. So I’m just gonna select that is gonna insert it on top of the other. We adjust the size and drag it and put it to the side of my report. So you can customize this in many different ways. If you wanna add more pages, you can add more pages here. Now this way I can filter, take a look at 2020, my year on year percentage changes, my sales year to date updates as well. Take a look at 2018. And if I get new data, all I have to do is replace the current invoice text file with the new invoice text file, and then click on refresh and everything is updated automatically. In case you need to do more complex calculations that you can’t find in. The quick measure are gonna need to write your own measures by clicking on new measure. To be able to write correct measures you need to learn the DAX formula language. Now just like with Excel, if you are familiar with Excel formulas, there is basic and there is advanced. So with just basic DAX, you can already do a lot of complex analysis. That’s a topic for another video. So I’m just gonna rename this double click, call it report and let’s save our file. So go to file and save, give it a name and save the file. If you wanna share your report with others so that they can interact with the report, you’re gonna want to publish, just go to publish right here and select a destination. This is gonna publish it to the power BI service. I’m just gonna publish mine to reports, click and select, and it’s publishing it to power BI. It might take some time. Once it’s done, you’re gonna see the success message right here to open it. You can directly click on this link. It’s gonna open up the browser and take you to your report on the web here. You can interact with the report on the web because check this out. We are on power bi.com. So if you’re logged into your office account, you can just type in powerbi.com and you can access your reports directly from the web. You have the option to share the report with others as well, either people in your organization, specific people, people with existing access and so on. You can also adjust some settings down here. Now you can also share this report in teams, right? So depending on the rights and where you publish it, you can add this report as a separate tab in teams. So over here in teams, I’m gonna go to this general channel and add a new tab for power BI. It’s gonna up my workspaces, select the report that I just made and save it as a separate tab. All the interactivity that we saw before is there. You can switch the years. You can filter by selecting an option from any of your visuals here as well. This wraps up our introduction to power BI. So as you probably noticed, just from looking at those available functionalities that were in the menu, there is a lot you can do in power BI. There’s a lot of different options that you have for importing and cleaning your data. There is a lot to relationships and writing your own DAX functions to do more complex calculations that you need for your data analysis. Now, the great thing is if you’re using power query and power pivot in Excel, you can easily apply that knowledge to power BI as well. So it’s much easier to get started.
Thanks for reading How to Use Power BI
15 Things You Didn’t Know About Bill Clinton