Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I need some assistance:
I have a table in PowerQuery which contains 18 columns. The table is looking like the table below. The "...." columns are representing all other columns in the table.
What I want is, to sum up all "Act YTD Savings" & "FC Savings" for All Plants per Month in a new row and the sum should be added to the same table. I already managed to sum the values by Month using the "Group" function, but I am stuck with adding the sums to the original table with all 18 columns, as the grouped table is not matching all columns. All other columns are not relevant for "All plants" sum row. They can either stay empty or be filled with a random value.
Date | Plant | .... | .... | ... | Act YTD Savings SUM | FC Savings Sum |
01.01.2022 | a | 5 | 5 | |||
01.01.2022 | b | 5 | 5 | |||
01.01.2022 | c | 5 | 5 | |||
01.01.2022 | d | 5 | 5 | |||
01.01.2022 | All Plants | 20 | 20 | |||
01.02.2022 | a | 6 | 3 | |||
01.02.2022 | b | 6 | 3 | |||
01.02.2022 | c | 6 | 3 | |||
01.02.2022 | d | 6 | 3 | |||
01.02.2022 | All Plants | 24 | 12 |
Your help is appreciated.
Thank you!
Solved! Go to Solution.
Hi @uie79957 ,
While I agree a 100% with @Fowmy, here still a solution in Power Query.
Before:
After:
The idea is to first do the group by and the sum calculations and afterwards append upon the query from the beginning. Make sure to name the new columns the same as the old ones so PQ can match them during the append. Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY/dCsAgCIVfJboeQ6399CzRRW3v/wzzRGODEDpK+nHUnD3xqk9IxDm/+KpiVUI+NGxdZfmTWmoqZCaE0wYvVQADV042eKsiGHyEJlBesI4Ww1Zgu/cZE9hGi2HbKwaIHXErw1aCDWJH3MroS/zA8gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Plant = _t, Col1 = _t, Col2 = _t, Col3 = _t, #"Act YTD Savings SUM" = _t, #"FC Savings Sum" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Plant", type text}, {"Col1", Int64.Type}, {"Col2", Int64.Type}, {"Col3", Int64.Type}, {"Act YTD Savings SUM", Int64.Type}, {"FC Savings Sum", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Act YTD Savings SUM", each List.Sum([Act YTD Savings SUM]), type nullable number}, {"FC Savings Sum", each List.Sum([FC Savings Sum]), type nullable number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Plant", each "All Plants"), #"Appended Query" = Table.Combine({#"Changed Type", #"Added Custom"}), #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Date", Order.Ascending}, {"Plant", Order.Descending}}) in #"Sorted Rows"
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @uie79957 ,
While I agree a 100% with @Fowmy, here still a solution in Power Query.
Before:
After:
The idea is to first do the group by and the sum calculations and afterwards append upon the query from the beginning. Make sure to name the new columns the same as the old ones so PQ can match them during the append. Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY/dCsAgCIVfJboeQ6399CzRRW3v/wzzRGODEDpK+nHUnD3xqk9IxDm/+KpiVUI+NGxdZfmTWmoqZCaE0wYvVQADV042eKsiGHyEJlBesI4Ww1Zgu/cZE9hGi2HbKwaIHXErw1aCDWJH3MroS/zA8gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Plant = _t, Col1 = _t, Col2 = _t, Col3 = _t, #"Act YTD Savings SUM" = _t, #"FC Savings Sum" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Plant", type text}, {"Col1", Int64.Type}, {"Col2", Int64.Type}, {"Col3", Int64.Type}, {"Act YTD Savings SUM", Int64.Type}, {"FC Savings Sum", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Act YTD Savings SUM", each List.Sum([Act YTD Savings SUM]), type nullable number}, {"FC Savings Sum", each List.Sum([FC Savings Sum]), type nullable number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Plant", each "All Plants"), #"Appended Query" = Table.Combine({#"Changed Type", #"Added Custom"}), #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Date", Order.Ascending}, {"Plant", Order.Descending}}) in #"Sorted Rows"
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
@uie79957
Power Query is meant for data transformation, grouping etc. You easily achieve this in Power BI using Matrix visual where you will get the subtotal.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |