COUNTIF multiple specific columns in POWER QUERY

New Contributor

Hi dears

 

 
markfikry_1-1634321405761.png

 

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

@markfikry 

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

image.png

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.

this is applicable for PowerBI? not working for me

@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"