Power Query - Allocate Amount (Reverse Running Total) -- simplification

[Comfy]

Hi,

I have created a proof of concept that allows me to allocate expenditure to different budgets (funding streams) based on a priority.

But the solutions feels abit clunky partly due to the final IF statement.

Does anyone have any better ideas of how to accomplish this?

Query - Source

let
    //Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZIxCsAgDEXv4uwQ00rp2muIW5dC196/xhrIUv0Gg394wkN+Si7wskbn3fFc91nuICvTMpWNRC77PssT7DbB1khUX/yxLAP6Kov4Kov4KlvOPtBdZEBdZRFdZRFdZSV2dHmiDZYd6Vp2pGtZib3v5Yk2WBbxRdtg2RoHvmgdLIv4onWw7FeH5ptf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, Type = _t, Period = _t, Project = _t, Fund = _t, Amount = _t, Budget = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", Int64.Type}, {"Type", type text}, {"Project", type text}, {"Fund", Int64.Type}, {"Amount", Currency.Type}, {"Budget", Currency.Type}, {"Period", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Amount", "Budget"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Only Selected Columns", each ([Value] <> 0)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Priority", each if [Fund] = null and [Attribute] = "Amount" then 0 else if [Fund] = null then 99 else [Fund]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Period", "Project"}, {{"Data", each Table.Sort(_,{{"Period", Order.Ascending}, {"Project", Order.Ascending}, {"Priority", Order.Ascending}}), type table}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "WithTotal", each CreateTable([Data])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"WithTotal"}),
    #"Expanded WithTotal" = Table.ExpandTableColumn(#"Removed Other Columns", "WithTotal", {"Account", "Type", "Period", "Project", "Fund", "Attribute", "Value", "Amount", "Priority", "RunningTotal"}, {"Account", "Type", "Period", "Project", "Fund", "Attribute", "Value", "Amount", "Priority", "RunningTotal"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded WithTotal", each ([Attribute] = "Budget")),
    #"Added Custom3" = Table.AddColumn(#"Filtered Rows1", "Actual", each if [Priority] = 7 then if [RunningTotal] > 0 then [RunningTotal] else 0 else if [RunningTotal] > [Value] then [Value] else if [RunningTotal] > 0 then [RunningTotal] else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Attribute", "Amount", "Priority", "RunningTotal"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Budget"}, {"Actual", "Amount"}})
in
    #"Renamed Columns"

Query - fxGroupedRunningTotal

(values as list, grouping as list) as list =>

let
    GRTList = List.Generate
    (
        ()=> [ GRT = values{0}, i = 0 ],

        each [i] < List.Count(values),

        each try if [i] = 0
            then [GRT = [GRT], i = [i] + 1]
            else [GRT = [GRT] - values{[i] + 1}, i = [i] + 1]
        otherwise [i = [i] + 1],

    each [GRT])
in
    GRTList

Query - CreateTable

(Source as table)=>

let

    List1 = Source[Period],
    List2 = Source[Project],
    Zipped = List.Zip({List1,List2}),
    ToTable = Table.FromList(Zipped, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Combined = Table.TransformColumns(ToTable, {{"Column1", each Text.Combine(_), type text}}),
   
    BufferedValues = List.Buffer(Source[Value]),
    BufferedGrouping = List.Buffer(Combined[Column1]),

    RT = Table.FromColumns(
        {
            Source[Account],
            Source[Type],
            Source[Period],
            Source[Project],        
            Source[Fund],
            Source[Attribute],
            Source[Value],
            Source[Priority],
            fxGroupedRunningTotal(BufferedValues, BufferedGrouping)
        },
        {
            "Account",
            "Type",
            "Period",
            "Project",        
            "Fund",
            "Attribute",
            "Value",
            "Priority",
            "RunningTotal"
        })


in
    RT

 

[GraH]

Hi,

Just looked at the if part you mentioned, and I think it can shortened like this

if [Priority] <> 7 then 0
else if RunningTotal] > 0 then [RunningTotal]
else if [RunningTotal] > [Value]  then [Value] 
else 0

PS: I do not not need running totals much, but I've seen shorter codes not requiring a custom function using list.generate (which I'm unable to master myself, and so I don't fully understand what yours does exactly.)

 

[Comfy]

Thanks GraH,

I'm not sure your restructure of the IF works but you have certainly helped to point me in the right direction as I can now see the duplication. Thanks!

And so I have revised the IF to the following:

if [RunningTotal] > [Value] and [Priority] <> 7 then [Value]
else if [RunningTotal] > 0 then [RunningTotal]
else 0

I've repurposed other code written to generate a running total and converted it so that rather than starting from zero and incrementing upwards. It starts at a ceiling and is reduced for the total being counted.

Using the #"Expand WithTotal" step from the source Query:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Account	Type	Period	Project	Fund	Attribute	Value	Amount	Priority	RunningTotal
2	12345	Build	1	11111		Amount	1100		0	1100
3	12345	Build	1	11111	1	Budget	500		1	1100
4	12345	Build	1	11111	2	Budget	500		2	600
5	12345	Build	1	11111	7	Budget	500		7	100
6	12345	Build	1	22222		Amount	900		0	900
7	12345	Build	1	22222	1	Budget	500		1	900
8	12345	Build	1	22222	2	Budget	500		2	400
9	12345	Build	1	22222	7	Budget	500		7	-100
10	12345	Build	1	33333		Amount	300		0	300
11	12345	Build	1	33333	1	Budget	500		1	300
12	12345	Build	1	33333	2	Budget	500		2	-200
13	12345	Build	1	33333	7	Budget	500		7	-700
14	12345	Build	2	11111		Amount	1900		0	1900
15	12345	Build	2	11111	1	Budget	500		1	1900
16	12345	Build	2	11111	2	Budget	500		2	1400
17	12345	Build	2	11111	7	Budget	500		7	900
18	12345	Build	2	22222		Amount	2200		0	2200
19	12345	Build	2	22222	1	Budget	500		1	2200
20	12345	Build	2	22222	2	Budget	500		2	1700
21	12345	Build	2	22222	7	Budget	500		7	1200
22	12345	Build	2	33333		Amount	3000		0	3000
23	12345	Build	2	33333	1	Budget	500		1	3000
24	12345	Build	2	33333	2	Budget	500		2	2500
25	12345	Build	2	33333	7	Budget	500		7	2000
Sheet4

For project 11111 in period 1 I want to allocate the 1100 "Amount" across the 3 different funds up to the maximum of the "Budget" value.
To do this I have used the list.generate to add the reducing running total to each row, this is the amount available for allocation (I repurposed code that was written for a normal running total)
I then use the if statement to say that if the remaining amount for allocation is greater than the budget the use the budget total.
The caveat being that Fund 7 budget can be exceeded. This is to ensure that the total allocation is allocated in full i.e. the Total of column [Value] by [Attribute] "Amount" equals the total of column [Value] by [Attribute] "Budget" (Green equals orange in the following)

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Account	Type	Period	Project	Fund	Attribute	Value	Amount	Priority	RunningTotal	Actual
2	12345	Build	1	11111		Amount	1100		0	1100	1100
3	12345	Build	1	11111	1	Budget	500		1	1100	500
4	12345	Build	1	11111	2	Budget	500		2	600	500
5	12345	Build	1	11111	7	Budget	500		7	100	100
Sheet4

The Fund 7 caveat is best shown by looking at project 33333 in period 2.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Account	Type	Period	Project	Fund	Attribute	Value	Amount	Priority	RunningTotal	Actual
22	12345	Build	2	33333		Amount	3000		0	3000	3000
23	12345	Build	2	33333	1	Budget	500		1	3000	500
24	12345	Build	2	33333	2	Budget	500		2	2500	500
25	12345	Build	2	33333	7	Budget	500		7	2000	2000
Sheet4

The proof of concept I have works with a small dataset but I'd like to try and clean it up/re-write in a different way so that it performs better using a real world data set.

 

[GraH]

Thanks for the feedback. Glad I could push a tiny little bit
I understand better now where you're adding. Can't help much more here I'm afraid.
For sure others will jump in and provide stunning M.

 

[Bo_Ry]

Not sure about this.
Maybe try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grouped = Table.Group(Source, {"Account", "Type", "Period", "Project"}, {{"A", each Table.AddColumn(_, "Actual", (x)=>  List.Max({ if x[Priority]=List.Max([Priority]) then x[RunningTotal] else 0  ,List.Min({x[Value],x[RunningTotal]}),0 }) )}}),
    Combine = Table.Combine(Grouped[A])
in
    Combine

PQ Reverse Running Total.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Account	Type	Period	Project	Fund	Attribute	Value	Amount	Priority	RunningTotal	Actual
2	12345	Build	1	11111		Amount	1100		0	1100	1100
3	12345	Build	1	11111	1	Budget	500		1	1100	500
4	12345	Build	1	11111	2	Budget	500		2	600	500
5	12345	Build	1	11111	7	Budget	500		7	100	100
6	12345	Build	1	22222		Amount	900		0	900	900
7	12345	Build	1	22222	1	Budget	500		1	900	500
8	12345	Build	1	22222	2	Budget	500		2	400	400
9	12345	Build	1	22222	7	Budget	500		7	-100	0
10	12345	Build	1	33333		Amount	300		0	300	300
11	12345	Build	1	33333	1	Budget	500		1	300	300
12	12345	Build	1	33333	2	Budget	500		2	-200	0
13	12345	Build	1	33333	7	Budget	500		7	-700	0
14	12345	Build	2	11111		Amount	1900		0	1900	1900
15	12345	Build	2	11111	1	Budget	500		1	1900	500
16	12345	Build	2	11111	2	Budget	500		2	1400	500
17	12345	Build	2	11111	7	Budget	500		7	900	900
18	12345	Build	2	22222		Amount	2200		0	2200	2200
19	12345	Build	2	22222	1	Budget	500		1	2200	500
20	12345	Build	2	22222	2	Budget	500		2	1700	500
21	12345	Build	2	22222	7	Budget	500		7	1200	1200
22	12345	Build	2	33333		Amount	3000		0	3000	3000
23	12345	Build	2	33333	1	Budget	500		1	3000	500
24	12345	Build	2	33333	2	Budget	500		2	2500	500
25	12345	Build	2	33333	7	Budget	500		7	2000	2000
RevRuningTotal

 

[Comfy]

Thanks Bo_Ry,

I really like that and will replace the IF statement. I can never remember how to pass the group to itself as a variable for the generator function so I'll have to put this in my reference file for later

Latest M for Query "Source"

let
    //Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZIxCsAgDEXv4lxKTCvFtdcQty6Frr1/TWsgS/UbDP7hCQ/5KTnPyxrc5Pb7vI5ye1mZmqlsIHJ5arM8wG4DrEQONMf4PvrDWQZUVhZRVhZRVracSNTUXWRAXWURXWURXWUlNnR5oBCW7elatqdrWYmt7+WBNlgW8UXbYNk3dnzROlgW8UXrYNmvDtU3Pw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, Type = _t, Period = _t, Project = _t, Fund = _t, Amount = _t, Budget = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", Int64.Type}, {"Type", type text}, {"Project", type text}, {"Fund", Int64.Type}, {"Amount", Currency.Type}, {"Budget", Currency.Type}, {"Period", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Amount", "Budget"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Only Selected Columns", each ([Value] <> 0)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Priority", each if [Fund] = null and [Attribute] = "Amount" then 0 else if [Fund] = null then 99 else [Fund]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Period", "Project"}, {{"Data", each Table.Sort(_,{{"Period", Order.Ascending}, {"Project", Order.Ascending}, {"Priority", Order.Ascending}}), type table}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "WithTotal", each CreateTable([Data])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"WithTotal"}),
    #"Expanded WithTotal" = Table.ExpandTableColumn(#"Removed Other Columns", "WithTotal", {"Account", "Type", "Period", "Project", "Fund", "Attribute", "Value", "Amount", "Priority", "RunningTotal"}, {"Account", "Type", "Period", "Project", "Fund", "Attribute", "Value", "Amount", "Priority", "RunningTotal"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded WithTotal", each ([Attribute] = "Budget")),
    Grouped = Table.Group(#"Filtered Rows1", {"Account", "Type", "Period", "Project"}, {{"A", each Table.AddColumn(_, "Actual", (x)=>  List.Max({ if x[Priority]=List.Max([Priority]) then x[RunningTotal] else 0  ,List.Min({x[Value],x[RunningTotal]}),0 }) )}}),
    Combine = Table.Combine(Grouped[A]),
    #"Removed Columns" = Table.RemoveColumns(Combine,{"Attribute", "Amount", "Priority", "RunningTotal"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Budget"}, {"Actual", "Amount"}})
in
    #"Renamed Columns"

 

[GraH]

Hi Bo_Ry or Excel Wizard, you're here too? You do like to use

(x)=>

type constructions. Somewhat a slippery slope for me...

 

[Comfy]

The problem I have is that I don't understand how M establishes what the x object contains.

I guess I have some reading to do.

 

[Bo_Ry]

Hi Grah.

(x) = > x is called Custom function and this is the full form of each

This is a must when you need to refer to something on other tables or records.

Please check Mike's video with a great explanation.

 

[GraH]

EDIT: I pressed enter, and at the same time Bo_Ry posted his reaction.

@Comfy,
how I understand it... (I also have it hard to completely get it though)
x is the table from the Table.Group, so the sub element. (x) => is much like a custom function construction. What's after the "=>" is then applied on each sub table.
It is a condensed way of doing transformations on a Group By All Rows via the UI.

@Bo_Ry , would this be close?