Power query help for adding conditional count column

[PSB]

@Greg_Deckler 

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

[Ricardoncosta · Accepted 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!

[Ricardoncosta · Accepted 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]

@amitchandak  Could you please help resolving this either by DAX or Power Query?

[amitchandak · Accepted solution]

@PSB , DAX, You can try a new rank column

Column = RANKX(Data,[Brand] &[Store],,ASC,Dense)