Connect to dataset from Excel as a TABLE, not PIVOT

[michaelshparber]

How can I connect to Power BI dataset and get data to Excel TABLE.

I know how to connect by Pivot via "Analyse in Excel", but I need a more "flat table" connection, without pivot drag and drop.

I know it can be done - what is the easiest way?

[Anonymous]

I have found a better and more correct way of solving this (without hacks) in 5 steps:

1. Create a PowerBI Report with a Table visualized in the report with the exact columns you want. (NOTE: The order in which you add columns will be the order in which they appear in Excel!! How they are visualized in PowerBI has no effect on the order!)

2. Publish the PowerBI Report with the table to PowerBI Online.

3. Open the Online PowerBI report in a browser.

4. Above the table, click the 3 dots ("...") menu and select "Export Data"

5. Select option "Summarized data" and file format ".xlsx (Excel) with live connection..." and click "Export" to download the Excel sheet with this table integrated. (Note that you cannot download this Excel if the Report owner has not allowed for download in the PowerBI Online Report Settings)

You can now click refresh in Excel to get it updated whenever needed!

Good luck!

Best regards,

Eirik Y. Øra, Oslo, Norway

[NotHappyAtAll]

Goto your Power Bi site on the web server, and select your Dataset.

Select one of your tables on the right side.
Then on top find the 'Show query' - and 'Copy to clippoard'.
    

Next step is to Open Excel and goto Data tab.
Inport data to a Pivot table - and selct your Power Bi data set (Source) in Excel.
Then doubble-click on any number in your pivot to generate a new worksheet.
Right click this new sheet and select Edit Query.
Here you replace the Command Text with the copied query from Power bi web service.  

I was actually looking for the solution to change these headers, and has posted the question on Stack Overflow:
https://stackoverflow.com/questions/75874394/how-to-get-data-from-a-power-bi-dataset-into-a-excel-ta...

[luisrh]

Well,   the same applies to a dataset in PBI.   All he has to do is change the connectionstring and use the XMLA endpoint to that dataset.

[Anonymous]

Hello @RobertSlattery @michaelsh  I am trying as well to flatten that dataset published onto Powerbi service into excel tables. I used the Analyse-In-Excel function to have the direct connection with my local excel. However, after I drilled-down on a measure in my pivot table and I edited the dax query, I obtained the following message:  

I've also tried by editing the ODC file with the changes in XML you outlined but I obtained the same message but this time starting by "Query (1,1)". Would you have any idea how I could resolve this?

[michaelsh]

@Anonymous 

Looks you have some syntax issues with Ekimetrics measure or column, you should probably check the DAX

[michaelsh · Accepted solution]

Ok, here is an excellent solution by @marcorusso  , who answered my qustion on his youtube video (link below):
Do this:

- open the pivot table

- put a measure

- double click a number in the pivottable

- you get a new worksheet with the details of the measure you double-clicked

- right click any cell of the table you see and click "Edit Query..."

- replace the query content with

EVALUATE <tablename>

where <tablename> is the name of a table.

You will get the entire content of the table back.

You can use any valid DAX table expression, there.

You can create DAX queries using DAX Studio.

Paste the code there, you have your data in a flat table - and it is amazingly fast!
https://www.youtube.com/watch?v=3fAE_qQCxZg

Boom! 🙂

Thanks, Marco!

[IamTDR]

Can this still be used today?  Sometimes I would like to have a table of my dataset in excel to create excel spreadsheets that could be shared and up to date. 

[DairyG1]

Hello All,

Can anyone get this to work and if so would you mind sharing screen shots?

Many Thanks

[RobertSlattery]

You can also export a connection file for the connection to the PBI service and edit the xml in the odc file.

First export the odb connection...

Then edit the XML...

Then just launch the odb file and excel will start with the connection you need.  Then you can right click on the connection and enter your dax query in the Command text window of the Connection properties (you could also do this in the XML) and your table will load.

If you want you can use the Performance analyser in PBI Desktop to copy the query of a visual and paste that into the Command text.

[nimdy]

This works great, but I'm finding that Excel creates the table with the datasource name and column name, e.g. if my data source in Power BI is called DataSource1 and my columns are Column1, Column2 and Column3, then Excel creates a table with headings DataSource1[Column1], DataSource1[Column2], DataSource1[Column3]. Is there any way to output Column1, Column2 and Column3?

I can't see anything in the ODC file I can amend. If I use EVALUATE DataSource1 in DaxStudio I get the required outputs, so it must be something Excel is adding in. 

[RobertSlattery]

I think it is the wrong youtube link (a useful one for other reasons though 🙂) but, it is the right solution. Thanks!  I was a bit discouraged when I followed your instructions and saw that Edit DAX... was greyed out and also when I saw an MDX query in the Edit Query... dialog.  However, sure enough, when I typed in a DAX query it works!

[michaelsh]

@RobertSlattery 

This is the link to Marco's video where I asked him this questions in the comments

You can see his reply there.

[RobertSlattery]

Oh ok. Thanks mate. Attention span!! 😊😎👍

[v-diye-msft]

Hi @michaelshparber 

There is no “Pivot Table” in Power BI but there is a “Matrix Visualization” which is almost equivalent to a Pivot Table in Excel. It offers nearly the same features as Pivot does in Excel but they are named differently and of course placed differently.

More reference please kindly refer to: 

https://www.goodly.co.in/create-pivot-table-in-power-bi/ 

Below is the similar threads could be your reference:

https://community.powerbi.com/t5/Desktop/Report-a-Table-like-Excel-Pivot-Table/m-p/565724 

[michaelshparber]

@v-diye-msft  This is not what I need.

I need to connect FROM Excel to an existing power bi dataset.

But I need the data to be imported to excel not as a pivot but as a flat table.

What is the way to do it?

[luisrh]

You can use DAX and use EVALUATE ('tableName')  to do that...

https://www.powerbi-pro.com/en/dax-query-tables-in-excel-2019/

[RobertSlattery]

The question is about accessing a PBI dataset.  Your solution is only connected to the local data model in excel.