Forum Discussion

Abhaykumar's avatar
Abhaykumar
Microsoft Employee
10 years ago
Solved

Getting distinct values for multiple columns

Is there a way to get distinct values for multiple columns? Specifically, I want to replicate the following SQL query into PowerBI to create a new table:   SELECT DISTINCT Col1, Col2, Col3 FROM Tab...
  • ankitpatira's avatar
    ankitpatira
    10 years ago

    Abhaykumar You can also select individual columns in query editor and click Remove Duplicates.

     

  • achinm45's avatar
    achinm45
    10 years ago

    Hi Abhaykumar,

     

    Use DAX and in DAX the function SUMMARIZE().

    Summarize will pull out distinct values from columns.

     

    So your resultset will be new table from Summarize

    Table_Output =  Summarize(Table_IN,Col1,Col2,Col3)

     

    I hope it helps !

     

    BR,

    Achin

ImkeF's avatar
ImkeF
Community Champion
10 years ago

So you have to turn your multiple columns into one list first.

List.Distinct(List.Union({Source[Col1], Source[Col2], Source[Col3]}))

Where "Source" is the name of the table/previous step 

Abhaykumar's avatar
Abhaykumar
Microsoft Employee
10 years ago

Thanks Imke for your reply. However I am getting the following error :

The expression is for creating a new table in Data view.

The error says :

A single value for column 'BlobName' in table 'ProdBlobInfo-Final' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

  • ImkeF's avatar
    ImkeF
    Community Champion
    10 years ago

    Sorry, should have mentioned that you need to add this column in the query editor, it's M-code.

    • Abhaykumar's avatar
      Abhaykumar
      Microsoft Employee
      10 years ago

      I tried to create a custom column using Query Editor. The column gets created as a List but gives error when expanded.

       

       

      Let me know if i am doing something wrong.