Create pivot table based on Power BI dataset

[CloudMonkey]

Hi,

Please can you tell me how to create a pivot table (or power pivot table?) based on a Power BI dataset?

Or is there a way to export the mapped dataset as a flat file?

Thanks,

CM

[Anonymous]

You can accomplish this only if you publish your dataset to the PowerBI.com service.

There, click on the options for dataset (...) and choose "Analyse in Excel"

You will probably need to install some driver (it'll guide you through - it is simple) and then it'll download a .odc connection file which you can open in excel and see Pivot connected to the service.

Good luck!

Michael

[CloudMonkey]

You can accomplish this only if you publish your dataset to the PowerBI.com service.
There, click on the options for dataset (...) and choose "Analyse in Excel"
You will probably need to install some driver (it'll guide you through - it is simple) and then it'll download a .odc connection file which you can open in excel and see Pivot connected to the service.
Good luck!
Michael

I've just tried this and I'm unable to drag a field into the values section of the pivot table. The numbers in this field are stored as integers (I see the numbers symbol in the column heading) in the Power BI database, but if I drag the field into the rows section of the pivot table and test the values with the "ISTEXT" formula the result is "TRUE". Therefore I suspect the pivot table isn't allowing the field into the values section of the pivot table because the pivot table thinks the field is a text field? Any idea how to fix?

Thanks,

CM

[Anonymous · Accepted solution]

@CloudMonkey

Did you use "Analyse in Excel" option as I've proposed?

If so - then Pivot should work.

You can only drag a Measure to the Values section - so create a measure, for instance:

In your FactTable there's a "Sales" column that contains the sales amount.

First make sure it is Decimal, then add a new Measure:

TotSales = SUM(FactTable[Sales])

That's it.

Now you can drag "TotSales" into the Values section.

It should work

Michael

[v-yulgu-msft]

Hi @CloudMonkey,

As you mentioned, all the fields stored in Power BI dataset will be recognized as Text type after being exported to pivot table.

Based on my test, we are not able to drag original fields into values section, but we can drag measures into values section. So, the workaround can be creating measures to get your desired values in advance.

Besides, you can submit your feedback to Microsoft at this page: https://ideas.powerbi.com/forums/265200-power-bi-ideas. And you can post the link back so that I can vote it for you.

Regards,
Yuliana Gu

[otravers]

There's an unsupported way to connect Excel to a local pbix file, you'll have to keep PBI desktop open alongside Excel:

http://exceleratorbi.com.au/excel-workbook-connection-local-power-bi-desktop/