BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
You can pull Excel workbooks (or individual data ranges) into Power BI Service, or push them from Excel using a special add-in. This blog shows how to use both approaches, as well as the underwhelming Analyze in Excel feature.
- Working with Excel in Power BI Service
- Analysing Data with Excel (this blog)
- Getting Data from Excel or CSV files in Power BI Service
- Publishing Excel workbooks, or tiling parts of a workbook
This blog is part of a longer series, which together comprise a full online training course in Power BI Service. You can see get details of our classsrom Power BI training courses here.
Posted by Andy Brown on 29 June 2017
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Analysing Data with Excel
You can choose to analyse any report or dataset in Excel, although all that this really means is that you get a pivot table.
This feature only works if you have Power BI Pro (the paid version of Power BI Desktop).
There are a lot of things which can go wrong when you try to analyse data in Excel (so much so that Microsoft have created a trouble-shooting page just for this feature). The end result is just a pivot table based on your data, so I'm not convinced it's worth the candle (a good English expression).
A couple of caveats first
Two warnings for this page:
| Warning | Explanation |
|---|---|
| Target audience | For such a simple concept, there's a lot of theory involved. This blog will give an overview of the steps involved, but you'll need to understand how to use Power BI Desktop and Excel to understand these fully. |
| Method used | Some people may be lucky enough to get an Excel pivot table appearing directly on screen. I'm not one of those fortunate people, so the method shown below explains how to turn the connection string generated into a pivot table instead. |
Creating a measure
This feature generates a pivot table based upon a cube. You won't be able to include anything in the Values for the pivot table unless you first create a measure:
Although I have a perfectly good numerical column in my dataset (NumberTests), I won't be able to report on it without incorporating it into a measure such as the one shown.
You then need to publish the report so that your measure is included in the dataset in Power BI Service.
if you haven't created a measure for, I'd suggest moving on to the next part of this Power BI Service blog!
Getting the connection string
It's easy enough to start to analyse data in Excel - simply select a report or dataset (both work), and choose this option:
Choose to create a pivot table in Excel based on the dataset for a report.
This feature creates a connection string, which I'm choosing to save:
Save your connection string as an ODC file.
I've then renamed this:
The renamed connection string file.
Creating a pivot table
In Excel, you can now create a pivot table, basing it on a connection:
Choose to base your pivot table on a connection you'll choose.
Browse for your connection, then choose the .ODC file you've created:
Click on this button to choose a connection, then double-click on the file you've just created.
You can now fill in your pivot table:
Choose what should go where in your pivot table.
The only fields you can include in the Values section are the measures you've created:
The TestCount measure is the only one we can aggregate.
Phew. Was it worth it? Personally, I don't think so!
- Working with Excel in Power BI Service
- Analysing Data with Excel (this blog)
- Getting Data from Excel or CSV files in Power BI Service
- Publishing Excel workbooks, or tiling parts of a workbook