Count amount of occurrence with filter in Power Query

Hi Anonymous 

Download example PBIX file

After seeing your data I decided to use another approach rather than the grouping I initially thought of.

Add a Custom Column in Power Query and you can do this with this line of code

= List.Count(List.Distinct(let _CustID = [Customer ID] in Table.SelectRows(#"Changed Type", each [Customer ID] = _CustID)[Unique Product ID]))

How it works:

This stores the Customer ID in a variable calld _CustID

let _CustID = [Customer ID]

This selects the rows in the table from the previous step (#"Changed Type" - you may have to change this to match your query) where the Customer ID = _CustID

Table.SelectRows(#"Changed Type", each [Customer ID] = _CustID)

This returns a list of the distinct Products each Customer uses

List.Distinct(.... , [Unique Product ID])

This counts the number of items in that list

List.Count

You'll notice my results are different to the ones you show in your sample data, but I believe mine are correct.  For example, Customer 1 uses Products A, D and C.  Customer 2 uses B, E, F and G.

Regards

Phil