Extracting Unique values into another powerpivot window

[masplin]

Wondering if there is any way to do this. I've been given a sheet of transactions with the customer name on every line i.e. they are duplicated thorughout. What I want is a table of unique customer names. The simple way to do this is create a pivot table with cusomtername as a row field and then copy paste the result into a n excel sheet and then link it back to powerpivot. However this isn't dynamic so wondering if there is anyway to dynamically extract a column of unique values form a table of duplicated values?

Thanks

Mike

 

[AlbertoFerrari]

Excel 2013 has the option to do this, using a DAX query as the source of an Excel table that you can then link back to PowerPivot. It works very well and will perform automatic refresh.
In PowerPivot 2010, this is harder. You can create your PivotTable, copy its content inside another table using Excel formulas and link back the Excel table.
I blogged something related to this in the past here: Alberto Ferrari : Dynamic Ranking with Excel and PowerPivot.
There, I spoke about ranking, but you can use the same technique to do distinct.
Keep in mind that, with this approach, you will need a two-step calculation of the workbook: once to compute the PivotTable and then once again to refresh your linked table.

Alberto
SQLBI

 

[masplin]

The second method was what I had in mind but just wanted ot be sure there wasnt another way. Thanks