Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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"
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.
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"
Thank you Zubair. This works perfectly!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |