Power pivot DAX formula to count unique items in one column for every row in another column

[Aleksandar89]

I am trying to solve problem. I have in my dataset among others also columns named customer and product.
Customer can have many products and entries are repeated. I need to have a new column to give number of unique products based on every row of customer column.
Dataset is huge and it's needed to be done in power pivot.

[TABLE="width: 302"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Product[/TD]
[TD]Distinct # of prod. For cust.[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]aaa[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]aaa[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]aaa[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]bbb[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]xxx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD]xxx[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD]yyy[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD]qqq[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[horseyride]

Choose customer column
Group .. choose advanced
Group by Customer
Count = Count Distinct Rows
add aggregation
Rows = All Rows
Expand resulting column and add Product Back

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Product", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}, {"Rows", each _, type table}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Product"}, {"Product"})
in
    #"Expanded Rows"