A maior rede de estudos do Brasil

How to build Power BI Dashboardsvideo play button

Transcrição


thank you for joining me today. I'm into Tracy and I'll be taking you through an action pack session where I will not one but two dash worlds using power bi I. This time I'm wearing my power bi I T shirt, which you can get from Microsoft's online store. In this webinar, I'm going to use the free power bi desktop tool to get some data from an Excel file using Power Query, which has built into power bi desktop. Then I will use the power pivot modelling tools, which also built into Power bi desktop to create some additional columns and calculations before creating the dashboards that you see behind me. Now we've got a huge amount to cover, so the pace will be too fast for you to take notes. Instead, I'm going to email you after the webinar ends with a link where you can download the video recording of the webinar on the Power Bi desktop file, an Excel workbook containing the data so you can give it a go yourself. Okay, let's crack on because we've got a lot of material to get through, so get comfy. First of all, let's take a look at the data will be working with here. I've got some sales data for two retail chains called Ready Where on Billings. These chains of based in Australia on the data spans from January 2016 through to July 2017. On the dim tables sheet, we have some look up tables that were used to group our sales data. For example, in Australia, our financial year runs from July through to June, so I've classified each date into its financial year on its financial quarter on financial year month. I've also got more information on the post codes so that we can see what suburb and state they relate to. And I've got a table that tells us the buyer for each product category on the manager for each suburb now in Excel, weeping climb to flatten all of this data into one big table so we could analyse it in pivot tables or using formulas on. We'd probably use the look up to add columns to this fact table, but empowered by we can use the power pivot modelling tools to create relationships between these separate tables on. Then we can analyze and visualize all of this data in the one model in power bi I and this is a much more efficient way to store and work with this data. Now, I should also point out that I have for mattered all of my data into excel tables on. I've given them each her name. That's meaningful. And this will help us when we bring the data into power. Bi, I will be able to easily identify the different tables based on their names. So now that you're familiar with the data, let's bring it into power. Bi desktop. So here I've got an empty party I desktop file. By the way, the file extension for Power bi desktop is Dr P. B. I X on the home tab in the external data group. We've got our power query tools on This is the same power crew tool you might be familiar with from Excel, except in power. Bi I The menu buttons have been collapsed down into just a few. I'm going to click on get data on notice that the first option is excel, which is what I want, but my way here, I'm going to click on more so I can show you that There's actually a lot Mawr sources we can connect to and get dated from. We've got allow different file times. We can connect to databases as your loads of online services. And then we've got some other services, like the Web and SharePoint, etcetera. You can even build power. They are reports using data from multiple sources on their adding more connectors all the time. So I'm going to go back to the file group and I'll choose Excel and then connect. Now we need to browse to L File. And there it is, there. So quick, Pimpin. Now the navigator gives May a list off the tables and sheets in this file. And if I had filtered lists or named rangers or even print dangers than they'd be listed here, too, remember I for mattered all of my tables into Excel tables, and we can see them denoted by this table icon. So I'm going to select each table and you see on the right I'll get a preview. We can take a closer look at the different data tables before we load them, and if we're happy, we can just collect load, and that will load it into Power pivot model in Power Bi desktop. I'm going to edit them first, and we can also take a closer look there. This simple is the power career window. Or just bring it into view and you can see on the left. We've got our five queries, one for each vowel tables that we've brought in from Excel. And if you've used PAL Korean Excel, you'll be familiar with this view. It looks a little bit different hedges because of the dark theme, but otherwise everything is pretty much the same. The only other difference is is close and apply in X, so we have clothes and load in power. Bi I we have close and apply, but essentially, it's the same bottom. Now the first thing I want to do is make sure that all the data types of correctly set we can tell what the data type is because each column has this icon in the top left. So this is a calendar icon for date, got text, whole numbers. Andi decimal numbers here so they will look fine. You can have a quick look through each of the different tables, and they look fine. So, Power Query has done its job incorrectly assigned the data types for me. I haven't had to do that. If you did want to change the data type of something going on the icon and you can choose a different data type or with the column selected on the home tab, we've got the data type up here is well, by the way, it's in this query editor that I do my data cleaning and editing. Andi. I want to reduce the data that I bring into power bi I as much as possible so that I'm not bloating my file with data I'm never going to use Now the other thing I need to do is add some columns to the sales table to calculate the total sales. Is that the moment I've got the sale price in the cost price on the total units? But I don't know the value of what I've sold somethinto ads and columns for the total sales and the costs. So upon the add column tab, I can go custom column. I'll give my calling my name and I build my formula. I can simply double click on the field, so the sale price times total units and I'll click. OK, we'll do another column for the cost. Onda again. Cost price Times total units on. Okay, now we'll do one for the gross profit. So Custom column on the gross profit is the sales minus the costs? Click. OK, so I've got my three new columns here. What I want to do is set the data type because at the moment they're set to any so on the home tab, I won't set them two decimal number. Okay, You can see in the right power Query has recorded the steps. So it's got my first custom column 2nd 3rd and then make changing the type. This is great, because when I add new data to that excel file, I can refresh the query. It will go and get the new data, and it will apply the same steps that new data didn't have to repeat this work over and over again. Okay, I think we're good to go or click close and apply. Power grew will load the data into the tables in the power bi model, which is effectively power pivot. It's a reasonable amount of data about or just over 70,000 lines of data in the fact table that contains all of our sales information. So it will take a few seconds initially. Okay, so our data is loaded on. Did we can see in the fields on the right hand side, we've got our five tables. I can click on the arrow and see the actual fields for those tables as well. Now there's three main areas in power bi desktop. Currently, we're in report view, weaken total through the different areas with these icons on the left. So we're in report view, and this is where we build our visualizations and our charts and tables and that kind of thing. Next we've got out date of you and this shows us the tables of data. We can toggle through different tables using the list in the fields. We can see the different columns date, and we can work with the data modelling side off power bi I in the data table view on the last one is relationship view on this lets us see how our tables of data are related. Let's make the sales table a bit bigger so we