Analyzing Data in Excel

>> So as well as taking our Excel content and publishing it up to a Power BI dashboard, I can actually use the data and reports that are already in Power BI but analyze them in Excel. So if I wanted to use pivot tables and use that more tabular view with actual text or even create Excel pivot charts, I can do that against my data that's sitting in the Power BI service. There's two ways of getting to that data, one is from the Power BI dashboards and the other is from within Excel.

So if I've got a report or a dataset that I want to analyze in Excel, all I need to do is click my little "…" menu and you'll see an Analyze in Excel option.

What you might need to do is download some updates for Excel. If you are using Office 365 and you're already always getting the latest versions of Excel, you may already have this installed or you can go and download these libraries here. I've already done that. So what you'll get when you click this one, you've installed those libraries is an ODC file or an Office Data Connection file. That will get downloaded and you can open it up and it will open straight up in Excel. When you enable that connection what happens is Excel opens up a connection to the dataset sitting in Power BI and it appears just as if you're connected to any other pivot table.

So you'll see all the fields that are available to you in the browser on the right-hand side. And I can start doing all of my analysis. So I can look at my year-to-date revenue, maybe slice that by country or I could put my products on as well. So I can see all of that different data and start analyzing it just as if I was working with it locally here within the Excel workbook. I can do the same thing with the pivot table. So let's take our data and create a new pivot chart. Let's take a stacked column chart of some sort. And in this case, I'm gonna look our product segments…

By country. You can see our moderation segment is the one that is selling the most, closely followed by convenience. So I can use all of the visualization capabilities within Excel to analyze data that sits within Power BI. Okay, the other way is that I can connect from here within Excel up to Power BI. And to do that, I'll start on a new sheet. I'll use the Power BI add-in for Excel. And on this ribbon, I've got an option to connect to data. And it lets me choose from the workspaces that I've got access to, the reports or the datasets that I want to connect. So let's use our worldwide sales report this time.

And when I get connect, I'll get presented with the same kind of thing, I'll get a pivot table chooser on the right hand side. And again, I can go and say, I kind of want to look at my year-to-date revenue. Let's choose some different this time, let's look out our time periods. So again, I'm just using pivot tables and pivot charts exactly the same as I would do with anything else in Excel, but this time I'm connected to data that's already published up to Power BI.

And of course, I can do any of the same things that I could do with this Excel spreadsheet, you know, share it around with my friends, send emails, or publish it back up to the Power BI service so that I can use these visuals against those data sources in my dashboards elsewhere.