Forum Discussion
Appended queries optimization
Dear all
I have 12 sales tables. (Sales by monthes)
Columns are: customer id, product id and amount.
I append all 12 tables (queries) and get final result with more than 5 million rows. Could you please help me to pivot all of this data. I would like to sum amounts for the same customer id and product id.
13 Replies
Are you saying that you want to pre-aggregate the data in Power Query before importing into your data model? Right click customer id in the Query Editor and choose Group By and then choose Advanced to group by two or more columns.
Dear Greg. Thank you for response.
I have 12 separate queries (12 excel uploads). I have appended them and get 1 one Yearly sales table with 12 amount columns. Then I have created new 'Grand Total' column and add up all these 12 monthes sales. Now I want to pivot my results related to this new 'Grand Total' column. Your solutions is good, but it works for native query columns. The columns which are added out of query editor will not be found in query ediyor window.
Perhaps this DAX Unpivot Quick Measure:
https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/m-p/574832
If not, please provide very simple sample/example data and expected result. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
