Forum Discussion
Solved
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...
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.
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