Forum Discussion

Narendrawinata's avatar
Narendrawinata
New Member
2 years ago

POWER QUERY = COUNTIF SAME CHARACTER

Hi,

I'm struggling to find out how to run CountIf Formula in Power Query. The purpose is to count a certain value in 2 or more column. Here's the example.


Thank you

Tutorial Requests

5 Replies

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years ago
        Table.AddColumn(
        your_table, 
        "TOTAL", 
        (x) => List.Count(
            List.PositionOf(
                Record.FieldValues(x),
                "P",
                Occurrence.All
            )
        )
    )
      • AlienSx's avatar
        AlienSx
        Icon for Super User rankSuper User
        2 years ago

        x is function parameter (variable) taking a value of current row of the table (in the form of record). Use code below if you feel comfortable to use each

          Table.AddColumn(
        your_table, 
        "TOTAL", 
        each List.Count(
            List.PositionOf(
                Record.FieldValues(_),
                "P",
                Occurrence.All
            )
        )
    )

         

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    2 years ago

    I don't know how to help you without your code and data. Most likely you don't know how to apply my code. This code works just fine:

    let
    your_table = #table(
        {"name", "01-apr", "02-apr", "03-apr", "04-apr"},
        {{"a", "P", "P", "P", "S"},
        {"b", "S", "P", "P", "P"},
        {"c", "S", "S", "P", "P"}}
    ),
    total = Table.AddColumn(
        your_table, 
        "TOTAL", 
        each List.Count(
            List.PositionOf(
                Record.FieldValues(_),
                "P",
                Occurrence.All
            )
        )
    )
in
    total

    regarding underscore: watch this