Forum Discussion

raddy's avatar
raddy
Frequent Visitor
8 years ago
Solved

Countifs in powerbi

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!

 

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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"

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      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's avatar
        raddy
        Frequent Visitor
        8 years ago

        Thank you Zubair. This works perfectly!