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 this in power query?

could anyone help?

Note: I make it in Excel with this equation =COUNTIF([@[KX01]:[KX05]],">95")

3 Replies

  • 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