Getting the data behind an Excel Pivot Table into Power BI

[TePe]

Hi,

I'd like to import data from an Excel Pivot Table. So to be clear: It's not a Power Pivot Model, it's not a table I have on a worksheet. It's created by giving Excel a data source (may be a cube...), Excel is loading this data into the pivot table (not the Power Pivot Model) and you can then work with the data. Unfortunately it looks that you need to bring all fields on a worksheet to be able to import the data into Power BI which is not an option for me (the worksheets are out of my control...). Any other idea?

[aabbondanza]

@TePe Hello!  Did you ever find a solution for this?  I have same issue where my source is just a 'read only' excel report with 3 pivot tables.  As an Excel user, i can double click to expand and retrieve the detailed data, but this source is not something we own or have access to (client provided). I need to automate the 'expand pivot table' step somehow in BI or PA to eliminate a user having to do that work before we can evaluate.  

[TePe]

Unfortunately, not that I remember... 

[daforlife123]

Does power bi have an option to get data from the underlying tabular data from a pivot table in Excel? Given you can usually just double click the totals rows in the Excel pivot table to show the tabular data, it seems like this shouldn't be an impossible update in one of the monthly releases. Does anyone know if this is currently possible because just like TePe there are instances where you don't have access to the direct data source but you do have access to just a pivot table. 

[v-yuezhe-msft]

@TePe,

Firstly, what options do you use to connect to the data source and load data into pivot table in Excel?

Secondly, use File->Import->Excel workbook contents in Power BI Desktop to connect to your Excel and check if you can get data.


Regards,
Lydia

[TePe]

Hi,

currently the data is coming from a "private" Analysis Server where I don't have access to. So a colleague is filling up the Excel Pivot table with data and provides some "standard report" as spreadsheets in the workbook. However the data behind the Pivot Table is much richer than what you see in the workbook. Power BI seems only to access the workbook's data (meaning: what you see on the different spreadsheets), not the data behind the Pivot Table... When I look at what's presented as a data source when you import Excel content you only see the pages of the workbook. So my hope is very limited that we can already import Pivot Tables' raw data...

Thanks,

[v-yuezhe-msft]

@TePe,

What options do you use to connect to the private SSAS in Excel? Have you used the option that I provide in my first reply to connect to the Excel?


Regards,
Lydia