cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NDWC
Regular Visitor

Pivoting a non-query table in Power Query Editor

I made a table "Results" using the "Enter Data" option and DAX language. The table is four columns combined from different original queries and filtered on multiple conditions based on fields in related queries. The columns are metricID, locationID, TimePeriodName, and ActualResult.  I need to pivot two of the four columns: TimePeriodName and ActualResult so that the values in TimePeriodName become columns and ActualResults values become the values for those columns while perserving the metricID, and locationID columns.

 

The problem: I see how to pivot data in the Power Query Editor but I do not know how to reference the "Results" table since it isn't a 'query' but a table constructed using DAX and several different original queries.

 

The Question: How can I reference a non-query table in Power Query? Or alternatively, how I can pivot the data the way I need to using DAX?

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

You cannot. Power Query cannot see tables created in DAX, nor tables from Live Connections. Power Query is an ETL tool - extract, transform, and load. Once it loads, what you do with that table on the other side in DAX, PQ cannot see those changes - so new calculated columns or calculated tables.

 

One exception is if you change a data type in Power BI. That actually goes back into Power Query and adds that Changed Type step.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

1 REPLY 1
edhans
Super User III
Super User III

You cannot. Power Query cannot see tables created in DAX, nor tables from Live Connections. Power Query is an ETL tool - extract, transform, and load. Once it loads, what you do with that table on the other side in DAX, PQ cannot see those changes - so new calculated columns or calculated tables.

 

One exception is if you change a data type in Power BI. That actually goes back into Power Query and adds that Changed Type step.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

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!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors