cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Countifs in powerbi

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

Raw table

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III

## Re: Countifs in powerbi

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}}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Custom.1", "Custom", "AllRows"})
in
#"Removed Columns"
Try my new Power BI game Cross the River
3 REPLIES 3
Highlighted
Super User IX

## Re: Countifs in powerbi

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.

---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Super User III

## Re: Countifs in powerbi

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}}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Custom.1", "Custom", "AllRows"})
in
#"Removed Columns"
Try my new Power BI game Cross the River
Highlighted
Frequent Visitor

## Re: Countifs in powerbi

Thank you Zubair. This works perfectly!

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors