Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am using Power Query to pivot a row into columns. After that I need to use the Group By feature to sum the new columns. When I update the file, the Pivot may create a different number of columns with different names. That will break the M code for the Group By. Does anyone have any solutions?
Solved! Go to Solution.
Hi @kingcondie,
Please refer to below steps applied in Query Editor mode.
1. Remove unnecessary column, [Date].
2. Group table.
3. Pivot column. Select [Size] column, then, click the "pivot" option under transform tab.
Each time source data is updated, the result table will be updated, too.
Best regards,
Yuliana Gu
Hey @kingcondie,
welcome to the forum 🙂
I'm aware that you are using Power Query, but can you pleae explain why you are doing this.
From the data you provided and the output you need it seems, that the output can be created easily using the matrix visual in Power BI.
It's alsways a good idea to keep the "long format" instead of converting a table into a wide format
Hey @kingcondie,
welcome to the forum 🙂
I'm aware that you are using Power Query, but can you pleae explain why you are doing this.
From the data you provided and the output you need it seems, that the output can be created easily using the matrix visual in Power BI.
It's alsways a good idea to keep the "long format" instead of converting a table into a wide format
Thanks for your response. I agree that the data should be kept in the long format. I need to pull data from the format and change the view to help with a scheduling program. I want to see how many of which sizes each item has and perfer to show it in the other format. I will reconsider if I can do further work and then pivot the columns. I am new to Power Query and very new to Power BI. It is amazing what can be accomplished with these fantastic tools! I will try to learn about the matrix visual as well. This may provide a solution to several challenges I have encountered while just using Power Query.
Clint
The Matrix view is a great way to show this report! I have a lot to learn. It looks like I need to spend some quality time with the walk throughs.
Glad it worked out for you.
After the walkthroughs start watching the
videos from guyinacube: https://www.youtube.com/channel/UCFp1vaKzpfvoGai0vE5VJ0w and the
videos from curbal https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw
Regards and have a nice weekend
Tom
Hi @kingcondie,
As I know, Group By feature is used to group values in row level, rather than columns. So, how do you want to sum up those new columns? Please provide sample data of original table and show us your desired result with examples.
Regards,
Yuliana Gu
Thank you for your response, v-yulgu-msft! In the example tables below, I pivot the size column and then group by item to get the result I need for dates 3/22/18 to 3/23/18. But the next day when I refresh the data, there will be a new date (3/24/18) and perhaps new sizes (B1315). I would like the new table to show columns for the new sizes along with the others. Is there a simple way to modify the M code so the Grouping will include all the pivoted columns and sum the values from quanity in each?
Data Table after both days
DateItemSizeQuantity
3/22/2018 | AAA | A911 | 10 |
3/22/2018 | BBB | A911 | 5 |
3/22/2018 | CCC | A911 | 15 |
3/22/2018 | DDD | A911 | 20 |
3/22/2018 | AAA | A1013 | 20 |
3/22/2018 | BBB | A1013 | 5 |
3/22/2018 | CCC | A1013 | 10 |
3/22/2018 | DDD | A1013 | 15 |
3/22/2018 | AAA | A1517 | 5 |
3/22/2018 | BBB | A1517 | 10 |
3/22/2018 | CCC | A1517 | 15 |
3/22/2018 | DDD | A1517 | 20 |
3/23/2018 | AAA | A911 | 15 |
3/23/2018 | BBB | A1013 | 10 |
3/23/2018 | CCC | A1013 | 20 |
3/23/2018 | DDD | A1517 | 5 |
3/24/2018 | AAA | B1315 | 20 |
3/24/2018 | BBB | A911 | 5 |
3/24/2018 | CCC | B1315 | 20 |
3/24/2018 | DDD | A1517 | 5 |
Result after 1st run (3/22/18 to 3/23/18)
ItemA911A1013A1517
AAA | 25 | 20 | 5 |
BBB | 5 | 15 | 10 |
CCC | 15 | 30 | 15 |
DDD | 20 | 15 | 25 |
Desired Result after 2nd run:
ItemA911A1013A1517B1315
AAA | 25 | 20 | 5 | 20 |
BBB | 10 | 15 | 10 | null |
CCC | 15 | 30 | 15 | 20 |
DDD | 20 | 15 | 30 | null |
Thank you for your continued help, v-yulgu-msft! Eliminating the date and grouping before pivoting makes the difference! I don't know if there is a simple way to build a list and then refer to that list when naming columns for grouping. If so, that would be a helpful trick. In the mean time, thank you for the answer!
Hi @kingcondie,
Please refer to below steps applied in Query Editor mode.
1. Remove unnecessary column, [Date].
2. Group table.
3. Pivot column. Select [Size] column, then, click the "pivot" option under transform tab.
Each time source data is updated, the result table will be updated, too.
Best regards,
Yuliana Gu
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |