Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
djburch15
Frequent Visitor

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!

djburch15_1-1717166927288.png

 

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

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

dufoq3_0-1717170494248.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

3 REPLIES 3
dufoq3
Super User
Super User

It works for data you provided. If you want me to add the code - provide new sample data please.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

djburch15
Frequent Visitor

@dufoq3 this appears to work perfectly when's there a single grouped category, however, I have seven separate grouped categories in practice and the above code doesn't seem to be able to reference the newly calculated Start_Value (End_Value = Adds + Removals) in future months

dufoq3
Super User
Super User

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

dufoq3_0-1717170494248.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.