Forum Discussion
Formula to sum columns based on criteria in another table
I am trying to use Power Query to add a new column with a formula that sums the values in a row from only a subset of the columns within the table. This subset of columns is based on yes or no criteria defined in another table. For example:
Table 1
a | b | c
1 | 2 | 3
Table 2
Category | Include? (Yes or No)
a | N
b | Y
c | Y
Ideal Table Outcome
a | b | c | Sum (of only Yes Categories)
1 | 2 | 3 | (=2+3) 5
This example is very simple but gets complex when multiple categories are listed in my second table and incoming data within the first table will contain variable column categories. Is there a way to do this with Power Query or is there a better approach to this?
=let a=Table.SelectRows(Table2,each [#"Include?"]="Y")[Category] in Table.AddColumn(Table1,"SumOfSelectedColumns",each List.Sum(Record.ToList(Record.SelectFields(_,a,1))))
3 Replies
- wdx223_Daniel
Community Champion
=let a=Table.SelectRows(Table2,each [#"Include?"]="Y")[Category] in Table.AddColumn(Table1,"SumOfSelectedColumns",each List.Sum(Record.ToList(Record.SelectFields(_,a,1))))
Thank you so much! I created a new blank query with this formula and it worked perfectly (once adjusting for my unique column titles).
As a follow-up, do you know if I can add a step in Power Query to format the columns that were summed (e.g. in the output table, the column headers would be filled in a different color)? This may be a useful way to visually double-check which columns were totaled.
- wdx223_Daniel
Power Query can not define visual format, it's only a query tool