Find the many ways to interact directly between Excel and Power BI Service
Part two of a four-part series of blogs

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.

  1. Working with Excel in Power BI Service
  2. Analysing Data with Excel (this blog)
  3. Getting Data from Excel or CSV files in Power BI Service
  4. Publishing Excel workbooks, or tiling parts of a workbook

This blog is part of a much 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.

Power BI Pro feature

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:

Creating 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:

Analyze in Excel

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:

Saving connection string

Save your connection string as an ODC file.

I've then renamed this:

Renamed connection

The renamed connection string file.

 

Creating a pivot table

In Excel, you can now create a pivot table, basing it on a connection:

Choosing 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:

Browse button

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:

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 pivot table field list

The TestCount measure is the only one we can aggregate.

 

Phew.  Was it worth it?  Personally, I don't think so! 

This blog has 0 threads Add post