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...
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