List.Accumulate & Table.AddColum with Iteration Step Counter

Hi Goodkat ,

When you use List.Accumulate, you don’t just have to pass the table along but you can also carry a counter with it, think of it Goodkat like keeping a little notebook that says “here’s my table so far, and here’s which loop I’m on.” Each time the loop runs, you add your new column to the table and bump the counter up by 1, that way, instead of hard‑coding {0}, you just use the counter value to pick the right component for that step.

Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡

🟩 Follow me on LinkedIn

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

Given the following tables (copied from your sample):

md_Calc

Tabelle11

The following will work as a new query:

let
    Source = List.Accumulate( 
        List.Buffer(Table.ToRecords(md_Calc)), 
        Tabelle11, 
        (state,current) => Table.AddColumn( 
            state, 
            current[Name], 
            each Record.Field(_,current[C1]) - Record.Field(_,current[C2]), 
            type number 
        ) 
    )
in
    Source

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

Hi @Goodkat ,
Thanks for reaching out to Microsoft Fabric Community.
Just wanted to check if the responses provided were helpful and if you had a chance to review the latest reply from @anilgavhane .

For additional reference, please refer to the following:

Excel Power query M use List.Accumulate and Text.Contains dynamic Column head - Microsoft Q&A
List.Accumulate - PowerQuery M | Microsoft Learn

List functions - PowerQuery M | Microsoft Learn

If further assistance is needed, please reach out.

Thank you.

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.