COUNTIF multiple specific columns in POWER QUERY

%3CLINGO-SUB%20id%3D%22lingo-sub-2851024%22%20slang%3D%22en-US%22%3ECOUNTIF%20multiple%20specific%20columns%20in%20POWER%20QUERY%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2851024%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22%22%3EHi%20dears%3C%2FP%3E%3CP%20class%3D%22%22%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22markfikry_1-1634321405761.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F317753iB8AB685C94ED1F71%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22markfikry_1-1634321405761.png%22%20alt%3D%22markfikry_1-1634321405761.png%22%20%2F%3E%3C%2FSPAN%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%20class%3D%22%22%3Ein%20no.%20of%20working%20machines%20cell%20(orange)%2C%20I%20need%20to%20count%20how%20many%20machine%20working%20from%20(yellow%20cells)%20with%20condition%20that%20the%20number%20should%20be%20more%20than%2095!%20how%20can%20i%20do%20this%20in%20power%20query%3F%3C%2FP%3E%3CP%20class%3D%22%22%3Ecould%20anyone%20help%3F%3C%2FP%3E%3CP%20class%3D%22%22%3ENote%3A%20I%20make%20it%20in%20Excel%20with%20this%20equation%20%3DCOUNTIF(%5B%40%5BKX01%5D%3A%5BKX05%5D%5D%2C%22%26gt%3B95%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2879272%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20multiple%20specific%20columns%20in%20POWER%20QUERY%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2879272%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1186080%22%20target%3D%22_blank%22%3E%40markfikry%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20hard%20to%20recognize%20something%20on%20such%20screenshot.%20For%20the%20model%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20390px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F319599i3927F8D9819A09B0%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ECOUNTIF()%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Inserted%20Count%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20%22Count%22%2C%0A%20%20%20%20%20%20%20%20each%20List.NonNullCount(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20List.Transform(%20%7B%5BA%5D%2C%20%5BB%5D%2C%20%5BC%5D%2C%20%5BD%5D%2C%20%5BE%5D%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20each%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20if%20List.Sum(%20%7B_%2C%200%7D%20)%20%26gt%3B%2095%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20then%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20else%20null%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20Int64.Type)%0Ain%0A%20%20%20%20%23%22Inserted%20Count%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EPlease%20check%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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"