Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kingcondie
Frequent Visitor

Pivot a Column then use Group By to Sum the new columns

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?

2 ACCEPTED SOLUTIONS

Hi @kingcondie,

 

Please refer to below steps applied in Query Editor mode.

 

1. Remove unnecessary column, [Date].

 

2. Group table. 

1.PNG

 

3. Pivot column. Select [Size] column, then, click the "pivot" option under transform tab.

2.PNG

 

Each time source data is updated, the result table will be updated, too. 

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

TomMartens
Super User
Super User

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.

something.png

 

It's alsways a good idea to keep the "long format" instead of converting a table into a wide format



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

8 REPLIES 8
TomMartens
Super User
Super User

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.

something.png

 

It's alsways a good idea to keep the "long format" instead of converting a table into a wide format



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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/2018AAAA91110
3/22/2018BBBA9115
3/22/2018CCCA91115
3/22/2018DDDA91120
3/22/2018AAAA101320
3/22/2018BBBA10135
3/22/2018CCCA101310
3/22/2018DDDA101315
3/22/2018AAAA15175
3/22/2018BBBA151710
3/22/2018CCCA151715
3/22/2018DDDA151720
3/23/2018AAAA91115
3/23/2018BBBA101310
3/23/2018CCCA101320
3/23/2018DDDA15175
3/24/2018AAAB131520
3/24/2018BBBA9115
3/24/2018CCCB131520
3/24/2018DDDA15175

 

Result after 1st run (3/22/18 to 3/23/18)

 

ItemA911A1013A1517

AAA25205
BBB51510
CCC153015
DDD201525

 

Desired Result after 2nd run:

 

ItemA911A1013A1517B1315

AAA2520520
BBB101510null
CCC15301520
DDD201530null

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. 

1.PNG

 

3. Pivot column. Select [Size] column, then, click the "pivot" option under transform tab.

2.PNG

 

Each time source data is updated, the result table will be updated, too. 

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.