Forum Discussion
Calculate Subtotals based on column value in Power Query M
Try 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)