Forum Discussion
Solved
Power Query Simple Recursive Custom Column Ending Value
Hi everyone,
I'm having trouble creating a simple recursive formula that allows me to reference a newly created column value as the starting value in a subsequent calculation. See the excel based screenshot below — I need to create a summarized End_Value based on activity during the Month, then conditionally reference that End_Value by ID in order to use it as the Start_Value in the following Month. I've highlighted month 1 ending values and month 2 starting values in green for illustrative purposes. Any advice here would be greatly appreciated!
Hi djburch15, check this:
- If you don't need to preserve sort order, delete steps AddedIndex, SortedRows and RemovedColumns (it will increase speed if you have bigger dataset).
- If you want to calculate also Start_Value, you can create new custom colum: End_Value minus Adds minus Removals
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7RCcAwCEXRXfxOQJ9Ih5Hsv0aN2iIk/hy8iTuJCC3KyxwTFmOL0VlOABqRKHds40RVbTT+17cUVvZux3myCR7Nltz8pIIt/Y9Z01mz+cMWS1EbtRZBPRR0Xg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Month = _t, Start_Value = _t, Adds = _t, Removals = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Month", Int64.Type}, {"Start_Value", type number}, {"Adds", type number}, {"Removals", type number}}), AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type), fn_EndValue = (myTable as table)=> [ a = Table.Buffer(Table.SelectColumns(myTable, {"Start_Value", "Adds", "Removals"})), lg = List.Generate( ()=> [ x=0, y = List.Sum(Record.ToList(a{x})) ], each [x] < Table.RowCount(a), each [ x = [x]+1, y = [y] + List.Sum(Record.ToList(a{x})) ], each [y] ), b = Table.FromColumns(Table.ToColumns(myTable) & {lg}, Value.Type(myTable & #table(type table[End_Value=number],{}) ) ) ][b], GroupedRows = Table.Group(AddedIndex, {"ID"}, {{"All", each fn_EndValue(_), type table}}), CombinedAll = Table.Combine(GroupedRows[All]), SortedRows = Table.Sort(CombinedAll,{{"Index", Order.Ascending}}), RemovedColumns = Table.RemoveColumns(SortedRows,{"Index"}) in RemovedColumns
3 Replies
Hi djburch15, check this:
- If you don't need to preserve sort order, delete steps AddedIndex, SortedRows and RemovedColumns (it will increase speed if you have bigger dataset).
- If you want to calculate also Start_Value, you can create new custom colum: End_Value minus Adds minus Removals
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7RCcAwCEXRXfxOQJ9Ih5Hsv0aN2iIk/hy8iTuJCC3KyxwTFmOL0VlOABqRKHds40RVbTT+17cUVvZux3myCR7Nltz8pIIt/Y9Z01mz+cMWS1EbtRZBPRR0Xg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Month = _t, Start_Value = _t, Adds = _t, Removals = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Month", Int64.Type}, {"Start_Value", type number}, {"Adds", type number}, {"Removals", type number}}), AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type), fn_EndValue = (myTable as table)=> [ a = Table.Buffer(Table.SelectColumns(myTable, {"Start_Value", "Adds", "Removals"})), lg = List.Generate( ()=> [ x=0, y = List.Sum(Record.ToList(a{x})) ], each [x] < Table.RowCount(a), each [ x = [x]+1, y = [y] + List.Sum(Record.ToList(a{x})) ], each [y] ), b = Table.FromColumns(Table.ToColumns(myTable) & {lg}, Value.Type(myTable & #table(type table[End_Value=number],{}) ) ) ][b], GroupedRows = Table.Group(AddedIndex, {"ID"}, {{"All", each fn_EndValue(_), type table}}), CombinedAll = Table.Combine(GroupedRows[All]), SortedRows = Table.Sort(CombinedAll,{{"Index", Order.Ascending}}), RemovedColumns = Table.RemoveColumns(SortedRows,{"Index"}) in RemovedColumnsIt works for data you provided. If you want me to add the code - provide new sample data please.
