Calculate Subtotals based on column value in Power Query M

[Anonymous]

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.

[AnkitBI]

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"

[AnkitBI]

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.

[Anonymous]

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)

[AnkitBI]

Ok.. Solution was based on your sample data and assumption of not having multiple dots in Indexes.

I will check other options including 'StartsWith'.

Meanwhile you can try reach out to @ImkeF.

[Nathaniel_C]

@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.
Nathaniel