Forum Discussion

markfikry's avatar
markfikry
Copper Contributor
Oct 15, 2021

COUNTIF multiple specific columns in POWER QUERY

Hi dears       in no. of working machines cell (orange), I need to count how many machine working from (yellow cells) with condition that the number should be more than 95! how can i do t...
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 24, 2021

markfikry 

It's hard to recognize something on such screenshot. For the model like this

COUNTIF() could be

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Inserted Count" = Table.AddColumn(
        Source,
        "Count",
        each List.NonNullCount(
                List.Transform( {[A], [B], [C], [D], [E]},
                    each
                        if List.Sum( {_, 0} ) > 95
                        then _
                        else null
                )
        ),
    Int64.Type)
in
    #"Inserted Count"

Please check in attached file.

  • markfikry's avatar
    markfikry
    Copper Contributor
    Nov 12, 2021
    this is applicable for PowerBI? not working for me
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 12, 2021

      markfikry 

      It depends on your Source. You may copy/paste below as new query into Power BI Desktop, it shall work

      let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(Binary.FromText("i45WKqkoMVTSUTI0AJFwZGhgCiSL83NTDZVidcCqjMDCENIYSJqbw9UZGphBVRvBVINUGJkiGwZRZAgz1xim0gQsZwJXZwZSZgDmWkDVmijFxgIA",
            BinaryEncoding.Base64),
            Compression.Deflate)),
            {"T","A","B","C","D","E","U"}
    ),
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
            {
                {"A", type number},
                {"B", type number},
                {"C", type number},
                {"D", type number},
                {"E", type number}}
    ),
    #"Inserted Count" = Table.AddColumn(
        #"Changed Type",
        "Count",
        each List.NonNullCount(
                List.Transform( {[A], [B], [C], [D], [E]},
                    each
                        if List.Sum( {_, 0} ) > 95
                        then _
                        else null
                )
        ),
    Int64.Type)
in
    #"Inserted Count"

Resources