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
Syndicate_Admin
Administrator
Administrator

Add sub totals for certian columns in large table

Hi guys

 

I have a large table in Power Query. It list products that are sold and each products has variants. In this case sizes. I would like to have my "sold" column to be summarized in the bottom for each style. But had a difficulties finding a solution.

 

This is how it looks now:

ModellSizeSoldBoughtStock
Product AA200
Product AB700
Product AC1200
Product AD1800
Product AE1700
Product AF1200
Product AG500
Product AH300
     
     
Product BA100
Product BB700
Product BC1100
Product BD1500
Product BE1400
Product BF1000
Product BG400
Product BH100
     
     
Product CA2019
Product CB4077
Product CC70124
Product CD8064
Product CE60155
Product CF2052

 

My hope is to have it look like this

ModellSizeSoldBoughtStock
Product AA200
Product AB700
Product AC1200
Product AD1800
Product AE1700
Product AF1200
Product AG500
Product AH300
Total 76  
     
Product BA100
Product BB700
Product BC1100
Product BD1500
Product BE1400
Product BF1000
Product BG400
Product BH100
Total 63  
     
Product CA2019
Product CB4077
Product CC70124
Product CD8064
Product CE60155
Product CF2052
Total 29  

 

Is it possible?

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @Syndicate_Admin ,

You can try this query in excel power query editor for the source table, it is possible to achieve that but due to the 'null' value in other columns, the sort of size for each product could be different by default.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Modell", type text}, {"Size", type text}, {"Sold", Int64.Type}, {"Bought", Int64.Type}, {"Stock", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Modell"}, {{"Sold", each List.Sum([Sold]), type number}}),
    Append = Table.Combine({#"Changed Type",#"Grouped Rows"}),
    #"Sorted Rows" = Table.Sort(Append,{{"Modell", Order.Ascending}, {"Sold", Order.Ascending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",each [Modell],each if [Size] = null and [Bought] = null and [Stock] = null then "Total" else [Modell],Replacer.ReplaceText,{"Modell"})
in
    #"Replaced Value"

1.png2.png

3.png

 

Best Regards,
Community Support Team _ Yingjie Li
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

6 REPLIES 6
v-yingjl
Community Support
Community Support

Hi @Syndicate_Admin ,

You can try this query in excel power query editor for the source table, it is possible to achieve that but due to the 'null' value in other columns, the sort of size for each product could be different by default.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Modell", type text}, {"Size", type text}, {"Sold", Int64.Type}, {"Bought", Int64.Type}, {"Stock", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Modell"}, {{"Sold", each List.Sum([Sold]), type number}}),
    Append = Table.Combine({#"Changed Type",#"Grouped Rows"}),
    #"Sorted Rows" = Table.Sort(Append,{{"Modell", Order.Ascending}, {"Sold", Order.Ascending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",each [Modell],each if [Size] = null and [Bought] = null and [Stock] = null then "Total" else [Modell],Replacer.ReplaceText,{"Modell"})
in
    #"Replaced Value"

1.png2.png

3.png

 

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

jennratten
Super User
Super User

Hello - three different ways to accomplish this in Power Query are described in this article.  https://ssbi-blog.de/blog/technical-topics-english/3-ways-for-sums-over-columns-in-power-query/ 

I have tried this, but when i did the only sum i got was an total of all products and all sizes combined. And that is not what i need. I rly need to have them summarized product by product. And not all together. 

 

@Syndicate_Admin Why not just append just the summarized rows back to the original table and call it a day? So, start with the original table as a query. Reference this query and implement your grouping/totalling. Then Append this second table to the first table.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Fowmy
Super User
Super User

@Syndicate_Admin 

You can do it in Power BI using a table or matrix visual. Is there any reason why you need a Power Query solution?

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi, yes. I dont have access to Power BI, as of now i only have excel. It will change in the future tho. 

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.

Top Solution Authors
Top Kudoed Authors