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.
Hi DataVitalizer,
this is what I exactly want to achieve, to not hard-code {0}. In VBA it would be a simple Dim b as Byte and then a b = b + 1 counter.
But in PQ the only solution I can think of is a List.Accumulate again 'List.Accumulate(calc[Name], -1, (state, current) => state + 1)' But if I place that instead of 0 in between {} it provides me the final value, which is 1.
So how can I realize the 'little notebook' ?
Any further thoughts are appreciated!
Thank you! Best regards, Andreas