List.Accumulate & Table.AddColum with Iteration Step Counter

[Goodkat]

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 use. With some search in the internet I found knowledge bringing me to a partially working List.Accumulate solution. It iterates the names of the formulas to implement, puts them in with Table.AddColumn.

BUT: As I have two formulas in my example I do need some sort of counter that pulls the correct components as per the current iteration step. For testing purpose I have hardcoded it with 0 'calc[C1]{0}'. I twisted my head around it, but could not find a solution that will give e a counter of loop iterations 0 and 1.

Does the community have any idea?

Here is the test data: https://c.gmx.net/@324888734501700174/_g94hQZQtry0sTV3AFJ_ZA

I would be grateful getting some sort of direction to further explore!

Thank you & best regards, Andreas

[v-veshwara-msft]

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.

[anilgavhane]

@Goodkat 

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.

[Goodkat]

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

[MarkLaf]

Given the following tables (copied from your sample):

md_Calc

Name	C1	C2
2025 Act vs. Plan	2025 Act	2025 Plan
2026 Plan vs. PY	2026 Plan	2025 Plan

Tabelle11

ID	Month	2024 Act	2025 Act	2025 Plan	2026 Plan
495610	1	6.156940762	8.055920677	6.470320337	5.689772867
495610	2	9.70635054	6.507203128	5.226427919	5.0031694
495610	3	7.235247371	5.940120158	4.770960614	4.884088345
495610	4	8.893267926	7.090517716	5.694932065	5.3945645
495610	5	8.351761512	7.524996236	6.043894687	3.963692682
495610	6	8.829130111	9.033572978	7.255546982	4.082626652
495610	7	8.477186062	7.279652302	5.846840383	4.116458407
495610	8	5.555766599	6.09992529	4.899312225	4.303199459
495610	9	9.323976401	5.722346075	6.730976524	3.866511964
495610	10	5.899650418	0	5.910373536	3.335485583
495610	11	5.481153542	0	5.468399388	3.203746932
495610	12	11.26603808	0	5.48744931	3.10626242
495611	1	0.046076913	3.076208629	2.470736251	5.232304671
495611	2	0.629232048	3.144369381	2.525481316	5.102421534
495611	3	1.628240393	2.978449124	2.392218185	5.417475917
495611	4	1.770829326	8.218466996	6.600873603	4.987596737
495611	5	2.109459335	3.816509036	3.065327604	4.583238333
495611	6	3.85510988	6.196149561	4.976597229	4.512016468
495611	7	2.358908747	3.690024638	2.9637384	4.313548968
495611	8	1.554631722	4.150571775	3.333638704	4.287959397
495611	9	1.93934608	3.704081636	4.096627051	4.288813259
495611	10	2.177332093	0	5.93721383	4.085264481
495611	11	2.543733305	0	5.130923734	4.078352211
495611	12	2.116851862	0	4.611539966	3.792114871
495612	1	0	1.056134298	0.848261484	1.76275249
495612	2	0	0.475198527	0.381667945	1.718775676
495612	3	0	1.087174653	0.873192345	1.840620002
495612	4	0	1.394521144	1.120045602	1.835611945
495612	5	0.00404599	1.674376534	1.344818671	1.781316702
495612	6	0.035141586	2.098866732	1.685758915	1.790735681
495612	7	0.239838827	1.595095718	1.28114224	1.761307518
495612	8	0.432797027	2.143208661	1.721373278	1.849701182
495612	9	0.827946169	1.707241892	1.388339606	1.808060209
495612	10	0.921714382	0	1.568303216	1.709007968
495612	11	1.140211897	0	1.453766759	1.699973736
495612	12	0.934968987	0	1.435962916	1.727567395

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

[dufoq3]

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

[DataVitalizer]

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