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.
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.