Forum Discussion
Calculate Subtotals based on column value in Power Query M
I have been trying to find a solution for this, but I just can't get my head around it.
I want to do a kind of SUMIFS in Power Query Editor using M-Language.
So I have a table like this:
| Index | Row type | Value |
| 1 | Header | |
| 1.1 | Value | 100 |
| 1.2 | Value | 200 |
| 2 | Header | |
| 2.1 | Value | 200 |
| 2.2 | Value | 400 |
Now I would like to add another temporary column which would summarize the Value column on the rows with type "Value" based on the start of index.
So for example. Index "1.1" and "1.2" they start with "1" so on the first row I would like to see the summary of rows with index "1.1" and "1.2".
The result would be something like this:
| Index | Row type | Value | Sum |
| 1 | Header | 300 | |
| 1.1 | Value | 100 | 100 |
| 1.2 | Value | 200 | 200 |
| 2 | Header | 600 | |
| 2.1 | Value | 200 | 200 |
| 2.2 | Value | 400 | 400 |
So basically I want to see the subtotals of each group. How can I do this?
So I can do it like this:
= Table.AddColumn(#"PREVIOUS_STEP", "Sum", each List.Sum(Table.SelectRows(#"PREVIOUS_STEP", each Text.StartsWith([Index], "1"))[#"Value"]))
That adds a new column and nicely gives me a subtotal of all the rows that start with "1". But how can make this so that instead of hard coding the "1" in the code I can use the Index value in the current row?
I have tried something like this:
= Table.AddColumn(#"PREVIOUS_STEP", "Sum", each List.Sum(Table.SelectRows(#"PREVIOUS_STEP", each Text.StartsWith([Index], Record.Field(_, "Index")))[#"Value"]))
But that doesn't work, it just returns the total sum of all value columns in each row.
5 Replies
Anonymous ,
I would use an if statement to return either of the two different values depending on whether the value of the decimal zero or not.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
NathanielTry below. Might not be the best solutionn but gets the work done. Will Recheck for simpler approach.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJITUxJLQIylGJ1gCJ6ILGwxJzSVCBtaGAAFTVCEjWCihph6DZC0Q1Xh6LbBCQaCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, #"Row type" = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", type text}, {"Row type", type text}, {"Value", Int64.Type}}), #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Index", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Index.1", "Index.2"}), #"Grouped Rows" = Table.Group(#"Split Column by Character Transition", {"Index.1"}, {{"sum", each List.Sum([Value]), type number}, {"AllRows", each _, type table [Index.1=text, Index.2=text, Row type=text, Value=number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([AllRows],"NewIndex",1,1)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Index.2", "Row type", "Value", "NewIndex"}, {"Index.2", "Row type", "Value", "NewIndex"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"AllRows"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Sum", each if [NewIndex] = 1 then [sum] else [Value]), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"sum", "NewIndex"}), #"Merged Columns" = Table.CombineColumns(#"Removed Columns1",{"Index.1", "Index.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Index") in #"Merged Columns"Hi - Earlier solution is better if we don't have any Row Type Column. For your case, we can also use below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJITUxJLQIylGJ1gCJ6ILGwxJzSVCBtaGAAFTVCEjWCihph6DZC0Q1Xh6LbBCQaCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, #"Row type" = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", type text}, {"Row type", type text}, {"Value", Int64.Type}}), #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Index", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Index.1", "Index.2"}), #"Grouped Rows" = Table.Group(#"Split Column by Character Transition", {"Index.1"}, {{"SumRows", each List.Sum([Value]), type number}, {"AllRows", each _, type table [Index.1=text, Index.2=text, Row type=text, Value=number]}}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Index.2", "Row type", "Value"}, {"Index.2", "Row type", "Value"}), #"Added Custom" = Table.AddColumn(#"Expanded AllRows", "Custom", each if [Row type] = "Header" then [SumRows] else [Value]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"SumRows"}), #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Index.1", "Index.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Index") in #"Merged Columns"Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.
Thanks AnkitBI for your solutions, but not exactly what I was looking for.
I simplified my problem too much :) My indexes are way more complicated than that.
So I also have indexes like:
Index Row type Value Sum 1 Header 300 1.1 Value 100 100 1.2 Value 200 200 2 Header 1100 2.1 Value 200 200 2.2 Value 400 400 2.3 Header 500 2.3.1 Value 150 150 2.3.2 Header 350 2.3.2.1 Value 350 350 So I don't have any set maximum of how long the indexes can be... At the moment my biggest index is:
1.1.1.1.1.1 But I don't want to make a code that is limited to certain index size, it should be able to handle any size index. That's why using something like "StartsWith" would be ideal because it would just simply summarize all the values on rows who's index starts with the index on current row. (it can summarize header rows too, because they are blank / zero)
