Power Query - dynamic sum of columns

[Donbozone]

Hello,

I need to add a custom column that will sum specific columns from the same query. It is easy to assume that every time some new column appears it will not be included automatically, so I need to make a kind of mapping and to define all columns that may appear in the report and that need to be included in the sum of my custom column.

What I did was adding another query named as 'mapiranje' in which I have mapped all columns that may appear in the report.

Example:



In my main query named as 'Izveštaj', in this month I have 3 types of bonus payments: Bonus1, Bonus2 and Bonus3.

What I need is a formula which will iterate through mapping query and sum all found columns in my main query.

Example: Add custom column named 'SumBonuses', go to mapping query and iterate through column 'AllBonuses', pick up names from that column and check if there are columns with that names in my main query. Sum rows from each found column.

Tried something like this, but returns an error: A cyclic reference was encountered during evaluation

= List.Sum(
List.Transform(
List.Select(
Record.FieldNames(Izveštaj),
each List.Contains(mapiranje[AllBonuses], _)
),
each Record.Field(Izveštaj, _)
)
)

Anyone has an idea how to solve it?

Thank you.

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:
Power Query - dynamic sum of columns (report mapping) - OzGrid Free Excel/VBA Help Forum

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Donbozone]

I Apologize.

Thank you for informing me.

 

[smozgur]

It is easy to assume that every time some new column appears it will not be included automatically,

Can you assume those new columns will be placed at the certain place in your source data? For example, can you say something like this: "All columns after 5th column in the source are bonus columns"? Or, "All columns between 5th from the beginning and 3rd from the end are bonus columns"?

What you are asking is possible, but I am trying to approach in another way which would be better. Otherwise, you can find two different methods below doing that as you asked.

First one: I like the List.Accumulate() function, so here is the first approach (you need to place your own main table and secondary bonuses table in the codes):

let
    // Main data table
    Main = Excel.CurrentWorkbook(){[Name="Izveštaj"]}[Content],
    // Main data table column names
    MainBonuses = Table.ColumnNames(Main),
    // Bonus table
    BonusTable = Excel.CurrentWorkbook(){[Name="mapiranje"]}[Content],
    // AllBonuses column
    Bonuses = BonusTable[AllBonuses],
    // Find the matching bonus columns in the Main data table
    Matches = List.RemoveItems(MainBonuses, List.Difference(MainBonuses, Bonuses)),
    
    // Add a column with dynamic bonus totals
    Result = Table.AddColumn(Main, "BonusTotal", each List.Accumulate(Matches, 0, (state, current) =>  state + Number.From(Record.Field(_, current))))
in
    Result

Second one: Although I like the List.Accumulate() function and this approach takes more lines, this might be more efficient than the previous one. Note that it is the same code until and including the Matches identifier.

let    // Main data table
    Main = Excel.CurrentWorkbook(){[Name="Izveštaj"]}[Content],
    // Main data table column names
    MainBonuses = Table.ColumnNames(Main),
    // Bonus table
    BonusTable = Excel.CurrentWorkbook(){[Name="mapiranje"]}[Content],
    // AllBonuses column
    Bonuses = BonusTable[AllBonuses],
    // Find the matching bonus columns in the Main data table
    Matches = List.RemoveItems(MainBonuses, List.Difference(MainBonuses, Bonuses)),

    // Get all columns
    AllColumns = Table.AddColumn(Main, "BonusTotal", each _),
    // Update the column records for keep the matching bonus column names only
    MatchingColumns = Table.TransformColumns(AllColumns, {"BonusTotal", each Record.SelectFields(_, Matches)}), 
    // Update the column to sum the matching columns
    Result = Table.TransformColumns(MatchingColumns, {"BonusTotal", each List.Sum(Record.ToList(_))})
in
    Result

 

[Donbozone]

Hi and thanks for your help.

No, there is no specific rule where new possible columns will be placed. I forgot to mention that not only new possible bonuses would be a problem in manual sum, but also if some of existing misses next month, the error would be returned.

I will test the code and come back with results.

 

[Donbozone]

Meanwhile, just one question if I may. From my understanding, provided code should add custom column which will summarize all bonuses. But what about taxes and additional payments? The request is the same, ie.: Iterate through AllTaxes columns, find these columns in main query and sum values in custom column.

Is there a solution which would give me an option to refer to the headers of 'mapiranje' query (AllBonuses, AllTaxes and Additional payments) or I should repeat the code every time I need new custom column?

 

[smozgur]

Please provide sample data for all tables if you can, including Izveštaj, mapiranje, and also desired result with mentioned and matching columns. Therefore, I can understand what you exactly need.