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.
Power Query help to calculate count column.
if Store is same and Brand and type is also same for all rows then count should be 1. (from below example Apple)
If store is same but two different combination of Brand and Type then count should be 2 and so on. (From below example Samsung)
Data
Brand | Type | Store |
Apple | Iphone | NYC |
Apple | Iphone | NYC |
Samsung | S22 | NJ |
Samsung | S21 | NJ |
Samsung | S21 | NJ |
Result
Brand | Type | Store | Count |
Apple | Iphone | NYC | 1 |
Apple | Iphone | NYC | 1 |
Samsung | S22 | NJ | 2 |
Samsung | S21 | NJ | 2 |
Samsung | S21 | NJ | 2 |
Solved! Go to Solution.
Hi PSB!
You can do the column in two steps. First you want to do the distinct count group by brand, then you can expand the result.
In the advanced editor, it should look like this:
#"Grouped Rows" = Table.Group(#"Changed Type", {"Brand"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"All", each _, type table [Brand=nullable text, Type=nullable text, Store=nullable text]}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Brand"}),
#"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"Brand", "Type", "Store"}, {"Brand", "Type", "Store"})"All.Type", "All.Store"})
Cheers!
@PSB , DAX, You can try a new rank column
Column = RANKX(Data,[Brand] &[Store],,ASC,Dense)
Hi PSB!
You can do the column in two steps. First you want to do the distinct count group by brand, then you can expand the result.
In the advanced editor, it should look like this:
#"Grouped Rows" = Table.Group(#"Changed Type", {"Brand"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"All", each _, type table [Brand=nullable text, Type=nullable text, Store=nullable text]}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Brand"}),
#"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"Brand", "Type", "Store"}, {"Brand", "Type", "Store"})"All.Type", "All.Store"})
Cheers!
@amitchandak Could you please help resolving this either by DAX or Power Query?
@PSB , DAX, You can try a new rank column
Column = RANKX(Data,[Brand] &[Store],,ASC,Dense)
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 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |