I am using Excel Data Model where I have imported some data from an SQL Query. I am trying to do the excel equivalent of removing duplicates in a column.

So I saw the DISTINCT function exists and says: "Returns a one column table that contains the distinct (unique) values in a column, for a column argument."

That is exactly what I need, I have a column which has 2million rows but only about 4k distinct values/rows.

However when I go into the Calculation area and create the measure_distinct:=DISTINCT(TABLE_NAME[COLUMN_NAME]) it gives the following error: "Calculation error in measure 'TABLE_NAME'[measure_distinct]: A table of multiple values was supplied where a single value was expected"

I get that what I am trying to get is not a measure, it's a one column table and I probably shouldn't be using the DISTINCT function in the Calculation area. But I cannot figure out where or how to use it in order to get that column with unique values.

The objective is to import that column of distinct values into the Excel sheet.

Any help is greatly appreciated

Cheers

CodePudding user response：

As I said in my comments, this should really be done in Power Query. However, it can also be done using DAX.

You need to import a random table using Data/Existing Connections and then selecting one of the available tables, choosing 'Table' in the Import Dialog box.

Then, right-click in this table, select 'Table/Edit DAX', then 'DAX' in the Command Type dropdown and type this in the Expression box:

EVALUATE DISTINCT(TABLE_NAME[COLUMN_NAME])