Adding a column with formula to sum columns with a Yes or No criteria

[Kaitlyn07]

I have merged two data tables using PowerPivot to include information that gives columns a condition (Include Y or No). Now I would like to sum those columns with "Y" criteria for each row. For example:

Columns to Sum |   A    |    B    |    C    |  New Column (sum of "Y" associated columns) |

Include? (Y or N) |  Y    |    Y    |     N  |        |

Values1  |               1       |     2   |     3    |  = (1+2)= 3 |

Values2  |     2                 |     3   |     4    |   =(2+3)=5  |

etc.

I will also note that it is important to retain the Y or N associated with the column so that the column selection used in the formula can be verified. 

[m_dekorte]

Hi @Kaitlyn07 

Here's an approach, for you to try:

let
    Source = Table.FromRows(
        {
            {"Y", "Y", "N"},
            {1, 2, 3},
            {2, 3, 4}
        }, {"A", "B", "C"}
    ),
    getCols = (tbl as table, row as number, string as text) as function => 
        (rec as record) => try List.Sum( 
            Record.ToList( 
                Record.SelectFields(rec, Table.SelectRows( Record.ToTable( tbl{row} ), each [Value] = string )[Name])
            )
        ) otherwise null,
    getResult = getCols(Source, 0, "Y"),
    AddSum = Table.AddColumn(Source, "Custom", each getResult(_))
in
    AddSum

with this result

I hope this is helpful

[Kaitlyn07]

I got this to work with the example data table above. However, I want to apply this to a larger dataset that will change as more data is fetched. Do I need to enter all the row contents for this to work? (see the referenced section below) 

 {"Y", "Y", "N"},
            {1, 2, 3},
            {2, 3, 4}
        }, {"A", "B", "C"}

[m_dekorte · Accepted solution]

Oh no @Kaitlyn07 that was just a sample table.

Open the Advanced Editor for the query (table) you want to apply this to. Go to the end of the last line of M code (right above the "in" clause, you will find that on the second last row), place a comma there and press enter to go to a new line. Now copy and paste this code in.

    getCols = (tbl as table, row as number, string as text) as function => 
        (rec as record) => try List.Sum( 
            Record.ToList( 
                Record.SelectFields(rec, Table.SelectRows( Record.ToTable( tbl{row} ), each [Value] = string )[Name])
            )
        ) otherwise null,
    getResult = getCols(Source, 0, "Y"),
    AddSum = Table.AddColumn(Source, "Custom", each getResult(_))

Replace "Source" on the last two lines with the name that follows after the "in" clause and then put "AddSum" after the "in" clause in its place. That should do it.

Note that this code below takes a table reference as first argument, a zero based row index of where the Y's and N's are - if on row 1 then subtract 1 and enter a 0 - as second argument and finally the text to match, in your sample that is a: "Y" as third argument. Amend to your needs.

getCols( UpdatedNameHere, 0, "Y")

I hope this is helpful.

Please mark as solution when this has helped to resolve your query. Thanks!

[Kaitlyn07]

I have another row of criteria that I would like to add a summed column based on the criteria (similar to above). Is there a way I can change the code to reference row 2 of the table for the Y or N criteria for the second condition? 

[m_dekorte]

@Kaitlyn07 

Certainly, as I've tried to explain here:

Note that this code below takes a table reference as first argument, a zero based row index of where the Y's and N's are - if on row 1 then subtract 1 and enter a 0 - as second argument and finally the text to match, in your sample that is a: "Y" as third argument. Amend to your needs.

getCols( UpdatedNameHere, 0, "Y")

Therefore if you need the 2nd row subtract 1 and enter 1 as second argument, like so:

getCols( UpdatedNameHere, 1, "Y")

[Kaitlyn07]

Thank you @m_dekorte. I apologize, I am new to power query and the M language and am still learning the logic behind the code. 

I got this to work by merging queries with different arguments as you clarified above. Thanks for all of your help!

[m_dekorte]

No worries @Kaitlyn07 

Glad you got this working 👍

[Kaitlyn07]

Thank you m_dekorte!!!! I have been trying to solve this for weeks and finally got what I need. I cannot thank you enough.