cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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

Accepted Solutions
Highlighted
Resolver I
Resolver I

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

9 REPLIES 9
Highlighted
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/

 

 

 

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

@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?

Highlighted

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

Highlighted
Resolver I
Resolver I

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

Highlighted

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!

Highlighted

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.

Highlighted

@RobertSlattery 

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

You can see his reply there.

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors