Forum Discussion
Display Power BI dataset sa a table in Excel
Hi,
I was trying to get data from a published Power BI Dataset into an excel table because I need it to be formatted as a table, not a pivot table.
I couldn't find any option to load Power BI dataset to table. It always loads as a pivot table.
Any ideas ? Or am I just asking too much? Or maybe not using the right tool to create data exports to Excel? (csv exports from Power BI service reports aren't that easy to use for our end users)
- Hi,
No I haven't and I don't know how to do that.
Do you have an example of a query that does that?
If in Power BI Services you click on dataset on the right pane it will list of all tables within this dataset. Select one of them, Table Preview appears, and from it you may export the table in few formats, includes Excel. In Excel it will be as a range, Ctrl+L to transform to the table.
Or you mean not export but import one of the tables into existing Excel from the table in Power BI dataset published on services?
- I didn't know we could do that!!
I will try it right waway!
Alternatively you may export any visual from the based on dataset visual to Excel. 3-dots menu on top right of the visual -> export data->summarize data.
In Excel
Data-> Queries and connection->Properties of connection. Here in Definition tab in Command text box it'll be some DAX query. You may change it on any suitable for you, e.g. to have entire table just
EVALUATE 'MyTable'
With that you have Excel file with live connection to the table in dataset.
- I will try this as well. The export from Power BI Service Dataset page keeps failing.
You have some error (which one?) or that option is not available?
Also please check if export is allowed Export data from a Power BI visualization - Power BI | Microsoft Learn
Finally, if you need few tables to import or add them the existing file. Create connection as in previous post and export it as ODC file from properties. You may copy/paste such files editing them in Notepad to define another tables (or create one by one and export in Excel).
Open new file, Data -> Existing connections -> Browse for more -> select proper odc file->open to table. Repeat to add all tables from saved ODC.
