Display Power BI dataset sa a table in Excel

[Pascal Bellerose]

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)

[#2]

@Pascal Bellerose 

did you try getting the data using power query in excel?

[#3]

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?

[#4]

@Pascal Bellerose 

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?

[#5]

@Pascal Bellerose 

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.

[#6]

@Pascal Bellerose 

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.

[#7]

I didn't know we could do that!!
I will try it right waway!

[#8]

I will try this as well. The export from Power BI Service Dataset page keeps failing.

[#9]

@Pascal Bellerose 

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