Count distinct profile for each ID in M query

[ljx0648]

Hi guys,

I am a table of data similar to the one below:

ID	Assign to	Web Profile	Profile Attribute 1	Profile Attribute 2	More Attribute columns
1	A	A1	Y	N	…
1	A	A2	N	Y
2	B	B	Y	N
3	C	C	Y	Y
4	D	D1	Y	N
4	E	D1	N	N

You will see for each customer ID, it can be assigned to different representatives - One ID can have several representatives to service at the same time like ID 4.

Meanwhile, eahc ID can have multiple web profile created for different purposes. 

My goal of the exercise here is to add one column in my powberbi datatable by using M query to display the amount of unique/distinct web profile each ID has.

End result should looks like:

ID	Assign to	Web Profile	# Distinct Web Profile
1	A	A1	2
1	A	A2	2
2	B	B	1
3	C	C	1
4	D	D1	1
4	E	D1	1

Any tips are appreciated. 

Thank you!

[RossEdwards]

A simple approach could be to do the following:

1. Duplicate your table, giving it a new name.  This will be your new source query. Right click on it and disable "Enable Load"
2. Update your existing table to remove all its code and point to your newly duplicated source query
3. Right click on your newly duplicated source query and select "Reference".  Also disable 'Enable Load' on this table.
4. In this newly referenced query make use of the 'Group By' function like this:
   
5. Inside your Table, do a merge query with your newly created ID Count query, joining ID to ID like this:
   

You should have an outcome that looks something like this: