cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
michaelshparber
Helper V
Helper V

Connect to dataset from Excel as a TABLE, not PIVOT

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?

 

1 ACCEPTED SOLUTION
michaelsh
Helper V
Helper V

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!

View solution in original post

11 REPLIES 11
AdrienL08
New Member

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:  

AdrienL08_0-1608735556161.png

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?

@AdrienL08 

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

michaelsh
Helper V
Helper V

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!

View solution in original post

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...

image.pngimage.png

Then edit the XML...

image.png

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.

image.png

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.

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!

@RobertSlattery 

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

You can see his reply there.

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

v-diye-msft
Community Support
Community Support

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 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

@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
Resolver III
Resolver III

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

 

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

 

 

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors