Countifs in powerbi

[raddy]

Hello. I'm trying to achieve something as shown below.

Raw table

Buildings        Status        Trade

A                    Open         Archi

A                    Open         M&E

A                    Closed       Archi

B                    Closed       M&E

B                    Open         Archi

C                    Closed       Archi

Desired Table

Buildings        Archi-Open        M&E-Open

A                    1                          1

B                    1                          0

C                    0                          0

This could be done easily using countifs in Excel. I would like to know how to achieve this in power query. (Note: No. of buildings varies; not limited to A,B and C only)

Thank you in advance!

[Greg_Deckler]

Hmm, in DAX (equivalent of Excel) you would use CALCULATE with FILTER's. Let me see what can be done in M or @ImkeF might have a suggestion.

[Zubair_Muhammad · Accepted solution]

@raddy

You can use this

Please see attached file for steps

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfIvSM0DUo5FyRmZSrE6KIK+MaUGBkZmrnBx55z84tQUFOVOyMIoGpywme6MxZRYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Buildings = _t, Status = _t, Trade = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Buildings", type text}, {"Status", type text}, {"Trade", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Buildings"}, {{"AllRows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.SelectRows([AllRows],each [Status]="Open" and [Trade]="Archi")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Archi-Open", each Table.RowCount([Custom])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each Table.SelectRows([AllRows],each [Status]="Open" and [Trade]="M&E")),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "M&E Open", each Table.RowCount([Custom.1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Custom.1", "Custom", "AllRows"})
in
    #"Removed Columns"

[raddy]

Thank you Zubair. This works perfectly!