Module 8: Using Excel with Power BI

Module 8 > Using Excel with Power BI > Lecture

• The reason I’m going to show it is that it’s actually very relevant to an Excel user because you can actually create the contents.
• One of the options to create the contents will be using Excel.
• Even if you use one of the other options to create the content, everything you have learned so far in this series of modules will be relevant and will help you building this content even if you are not using Excel because the same data model.
• The same technology for queries, the same DAX expressions, are actually the way you build the content for this service, which is Power BI. Power BI comes as a freemium.
• Now, just before I show you anything I want to say that I’m going to show just a very narrow aspect of the capabilities of this service, because I’m actually showing the Excel part in it, and how to use Excel to upload into the server, or the service.
• That is actually showing data coming from which should be familiar because it’s actually using the same model that I created with Excel.
• I uploaded to this service the Excel in a way that actually the service is now using the data model with its connection, with its expressions, DAX expressions, everything I did but not the visualization.
• You can see, right away, that the variety of visualizations is beyond what you can do with Excel for example showing data on a map or using this tree map where it shows the area for each one of the manufacturers which represent their relative size in sales and so on.
• Now, the dashboard can contain tiles coming from different reports connected to different data sets.
• Actually the source I was using is a model in Excel, but if you want to you can add, and if you go back to my area and to the dashboard I was saying that the dashboard can contain tiles coming from and connecting to any one of these sources.
• So I can go here and change its visualization to something else, for example, to this kind of map, in which the area is actually colored and the intensity of the color represents again the measure that I want to visualize.
• From the report, I could now, all this actually elements here are already part of the dashboard.
• Just to show you when I’m looking at one of these elements in the report I can pin it and it will go and pin to the dashboard which is the current dashboard.
• Now in the dashboard I can edit some properties of it, I can give it a title and, but now this becomes part of the dashboard, and again, if I click on it, it will go back to the report that it came from.
• Now in order to create the content, as I said this content I created with the Excel but there’s also a companion application called Power PI Desktop that you can use it instead of Excel to create the content, to import the data using queries to create the DAX expressions, calculate columns, calculate measures, you can do it with Power PI desktop.
• I’m going to the Get Data, and I’m saying that I want to bring data from a file.
• It knows where my files are, and I have a folder here called For Excel Training, in which I’ve stored some material, and it’s actually a version of the same file, but I designate it as to be used as Excel, and we’ll see in a moment what it does.
• Once I decided I want to use this one, it actually tells me, asks me one of two options.
• What do I want to do? Do I want to import the Excel data into the Power BI service, and this is what I did before.
• To import means, use the model in this Excel, and then start to build reports and dashboards on top or I just want to connect to it as it sits in OneDrive for business and here I’m selecting the option to actually connect and it’s started to do that.
• It asks me, what do you want to do with it and you can see that there are options for example to schedule a refresh so I can actually schedule a refresh that will run everyday at a specific hour and actually pull data from the data sources and so on but what I want to do with it now is viewing it.
• Now what it does it opens the Excel file and I will be interacting with it in the similar way that I was interacting with it when I was in Excel.
• So all the slicers, all the functionalities available to me, only that now I am actually opening this Excel from the browser, and using the technology that we’ve had for quite some time called Excel Services or Excel Online.
• It’s only that it’s now integrated into the experience of Power BI. So again, I can upload Excel files in two different ways, one is to be used as the model, and to build dashboards and reports from it.
• One is to actually upload it as a report with the visualizations of Excel.
• In the Excel client if I switch back there is a publish option that can actually push it directly from Excel to the Power BI environment.