Forum Discussion
Goodkat
9 months agoHelper II
List.Accumulate & Table.AddColum with Iteration Step Counter
Dear Power Query Experts, Recently I stumbled over an idea to implement simple deviation calculations based on a master data sheet that specifies the name of the formula and the two components to...
- 9 months ago
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.
v-veshwara-msft
8 months agoCommunity Support
Hi Goodkat ,
Just checking in to see if you query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance.
Thank you.