Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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!
Solved! Go to Solution.
Hi @djburch15, check this:
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
@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
Hi @djburch15, check this:
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
15 | |
13 | |
12 | |
11 |