Search code examples
powerbipowerquerym

Create a column that counts all "Y" in all other columns for each row in Power BI

I am working in Power BI and have dataset with columns containing "Y", "N", or "NA". There are over 100 columns so I am trying not to create an if statement for every column. Is there any way that I can count the number of "Y" in each row for each column? With an example I could do this for the "N" as well. Thanks in advance. The result would be a new column that is a count of all "Y"s in the other columns. Here is what the output is in excel (but in reality I have over 100 columns/questions): enter image description here

Solution

  • I tried with this dataset:

    A B C D
    Y N Y NA
    N Y NA Y
    NA NA NA Y
    Y Y Y Y

    I went to Transform Data -> Add Column -> Custom Column.

    I used this code:

    let
    columnNames = Table.ColumnNames(#"Source"),
    customColumn = Table.AddColumn(#"Source", "Count of Y", each List.Sum(List.Transform(columnNames, (col) => if Record.Field(_, col) = "Y" then 1 else 0)))
in
    customColumn

    Replace #"Source" with the previous step in your Power Query process. You can also change "Count of Y" to the desired new column name.

    My result was:

    enter image description here