Forum Discussion
List.Accumulate & Table.AddColum with Iteration Step Counter
Instead of just iterating over the formula names, iterate over a list of records that include both the formula and its index:
let formulas = {"Deviation1", "Deviation2"}, components = { {"C1", "C2"}, {"C3", "C4"} }, baseTable = YourBaseTable, result = List.Accumulate( List.Zip({formulas, components}), baseTable, (state, current) => let formulaName = current{0}, compPair = current{1}, newCol = Table.AddColumn(state, formulaName, each [Record.Field(_, compPair{0})] - [Record.Field(_, compPair{1})]) in newCol ) in result
Explanation
- formulas is your list of formula names.
- components is a list of field pairs used in each formula.
- List.Zip combines them into a list of {formulaName, {component1, component2}}.
- List.Accumulate loops through and adds a new column each time.
- The counter is implicitly handled by the position in the zipped list.
Instead of just iterating over the formula names, iterate over a list of records that include both the formula and its index:
let formulas = {"Deviation1", "Deviation2"}, components = { {"C1", "C2"}, {"C3", "C4"} }, baseTable = YourBaseTable, result = List.Accumulate( List.Zip({formulas, components}), baseTable, (state, current) => let formulaName = current{0}, compPair = current{1}, newCol = Table.AddColumn(state, formulaName, each [Record.Field(_, compPair{0})] - [Record.Field(_, compPair{1})]) in newCol ) in result
Explanation
- formulas is your list of formula names.
- components is a list of field pairs used in each formula.
- List.Zip combines them into a list of {formulaName, {component1, component2}}.
- List.Accumulate loops through and adds a new column each time.
- The counter is implicitly handled by the position in the zipped list.
Dear anilgavhane & the Community,
as per a faulty webbrowser setup I could not reach the Fabric Community with this account. I constantly recreived an error 403 message. Now after a major update I am back again.
And thank you!!! Your solution works fantastic! I never thought this would be possible and now I am thrilled by way more possibilities in my current datamodel.
Ich will put your approach documented to my Power Query learning document.
Once again a big thank you!
Kind regards, Andreas