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 Goodkat, I've updated your code. It is dynamic and you can delete calc table.
Act vs. Plan = Actual Year vs Plan same year
Plan vs. PY = Current Plan vs. Plan Previous Year (it your sample it was different)
Result:
let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
RoundActAndPlanColsDynamic = Table.TransformColumns(Source, List.Transform(List.Select(Table.ColumnNames(Source), each List.Contains({"Act", "Plan"}, _, (x,y)=> Text.EndsWith(y,x))), (x)=> {x, each Number.Round(_, 1), type number})),
PlanYears = List.Sort(List.Transform(List.Select(Table.ColumnNames(RoundActAndPlanColsDynamic), each Text.EndsWith(_, "Plan")), each Text.Trim(Text.BeforeDelimiter(_, "Plan")))),
AddedCols = List.Accumulate(PlanYears, RoundActAndPlanColsDynamic, (s,c)=>
[ check1 = Record.HasFields(s{0}, { c & " Plan", c & " Act" }),
check2 = Record.HasFields(s{0}, { Text.From(Number.From(c)-1) & " Plan", c & " Plan" }),
Ad_ActVsPlan = if check1 then Table.AddColumn(s, c & " Act vs. Plan", each Record.Field(_, c & " Act") - Record.Field(_, c & " Plan"), type number) else s,
Ad_PlanVsPY = if check2 then Table.AddColumn(Ad_ActVsPlan, c & " Plan vs. PY", each Record.Field(_, c & " Plan") - Record.Field(_, Text.From(Number.From(c)-1) & " Plan"), type number) else Ad_ActVsPlan
][Ad_PlanVsPY])
in
AddedCols