Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Grouped Running Totals with a maximum condition and a verifier

Good evening, people.

I'm in need of a function on power query that sums the running total across its categories, but compares the running total value to a previously defined maximum for its category. If rt is greater than its maximum, it subtracts its own value and remains the same as (i-1). In addition, it should add 0 to a verification column.

I used this code from myonlinetraininghub (https://www.myonlinetraininghub.com/grouped-running-totals-in-power-query) and it solves part of my problem, but not the entirety of it

Here's a screenshot of an expected result:

I would be very pleased if anyone could help me with this. It doesn't seem to be difficult to solve, but I've tried several different solutions and none have worked, so I'm having a hard time with this.

Thanks in advance

Anonymous

Hi Anonymous

Here is one way, merge the maxTable to get Max value for each Group, then add RT and Verifier

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc2xDQAhDAPAXVJTgBNE/f9jIPZfA1Aku/nu5NjKnPZYsbBVUq2TTmHoDrGpkN33ppWE6CqAivz13RXVQ6TCScfP6Ght", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Group"}, maxTable, {"Group"}, "maxTable", JoinKind.LeftOuter),
    #"Expanded maxTable" = Table.ExpandTableColumn(#"Merged Queries", "maxTable", {"Max"}, {"Max"}),
    #"Grouped Rows" = Table.Group(#"Expanded maxTable", {"Group"}, {{"allrows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 
[a=Table.AddIndexColumn([allrows], "Index", 0, 1, Int64.Type),
b=Table.AddColumn(a, "RT", each List.Last( List.Accumulate( List.FirstN( a[Value],[Index]+1),{},(x,y)=>x&{{y, if List.Last(x,{0,0}){1}+y>[Max] then List.Last(x,{0,0}){1} else List.Last(x,{0,0}){1}+y}})){1}),
c=Table.AddColumn(b, "Verifier", each if [Index]=0 then 1 else if [RT]<>b[RT]{[Index]-1}+[Value] then 0  else 1)][c]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Group", "Value", "RT", "Verifier"})
in
    #"Expanded Custom"

I have maxTable

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/KLy1Q0lHyTaxQitWJVnIEsk3MwUwnENMSzHQGMg2NDJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Group", type text}, {"Max", Int64.Type}})
in
    #"Changed Type"

Both Queries you can paste in Advanced Editor via blank query, if you change table name, modify the merge maxTable accordingly.

RT is an adpotion of original post here: Running total that outputs "0" if the sum is negat... - Microsoft Power BI Community

Next time, provide your sample data in a format which people can copy.

1 Reply

Anonymous

Not applicable

Hi Anonymous

Here is one way, merge the maxTable to get Max value for each Group, then add RT and Verifier

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc2xDQAhDAPAXVJTgBNE/f9jIPZfA1Aku/nu5NjKnPZYsbBVUq2TTmHoDrGpkN33ppWE6CqAivz13RXVQ6TCScfP6Ght", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Group = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Group"}, maxTable, {"Group"}, "maxTable", JoinKind.LeftOuter),
    #"Expanded maxTable" = Table.ExpandTableColumn(#"Merged Queries", "maxTable", {"Max"}, {"Max"}),
    #"Grouped Rows" = Table.Group(#"Expanded maxTable", {"Group"}, {{"allrows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 
[a=Table.AddIndexColumn([allrows], "Index", 0, 1, Int64.Type),
b=Table.AddColumn(a, "RT", each List.Last( List.Accumulate( List.FirstN( a[Value],[Index]+1),{},(x,y)=>x&{{y, if List.Last(x,{0,0}){1}+y>[Max] then List.Last(x,{0,0}){1} else List.Last(x,{0,0}){1}+y}})){1}),
c=Table.AddColumn(b, "Verifier", each if [Index]=0 then 1 else if [RT]<>b[RT]{[Index]-1}+[Value] then 0  else 1)][c]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Group", "Value", "RT", "Verifier"})
in
    #"Expanded Custom"

I have maxTable

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/KLy1Q0lHyTaxQitWJVnIEsk3MwUwnENMSzHQGMg2NDJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Group", type text}, {"Max", Int64.Type}})
in
    #"Changed Type"

Both Queries you can paste in Advanced Editor via blank query, if you change table name, modify the merge maxTable accordingly.

RT is an adpotion of original post here: Running total that outputs "0" if the sum is negat... - Microsoft Power BI Community

Next time, provide your sample data in a format which people can copy.

Related content