Appended queries optimization

[OSS]

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.

[Greg_Deckler]

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.

[OSS]

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.

[tex628]

Add a date column to each query with the appropriate month and make sure that the other columns all have the same name ( Amount, Product, Customer). Then append the 12 queries and you should be able to show the values by dimension.

[OSS]

Actually I don't need any date for my queries. Before uploading I correct all my tables and make all columns the same name.

[Greg_Deckler]

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

[OSS]

Since my queries contain huge amount of data I try to create sample of the data in the excel.

I wonder, if merge function will be solution for me?

[tex628]

If you append with the names being the same this should be the result: 



From here you can easily show total amount per customer or product.

[OSS]

Your are right tex628.

This is exactly what I have now. But I want to see 31+10+19=60 for 202 Customer.

If product id and customer id are the same I would like to sum the amount. 

[tex628]

If you create a matrix and then use your columns like this:



Aren't you getting 60 for customer 202?

[OSS]

In matrix view everything is ok. But, after appending I have 5 million rows. I want to merge these rows, sum amounts and get less row amount. Is it possible to group, or pivot, or merge my data on data level, not report level?

[tex628]

Try this: 

Left click on a column in power query and choose group by. 

Use these settings:

[OSS]

This is also doesn't work 😞  We have already discussed this issue in the previous message

[v-chuncz-msft]

@OSS,

You may also try SUMMARIZE and GROUPBY in DAX.