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

achinm45's avatar
achinm45
Advocate IV
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

If you do it in DAX, just be aware that the full table must be loaded into the data model. Doing it in the query-editor using M would (mostly) fold back to the server, meaning that the SQL-server does the extraction and returns only the results to PBI to be loaded.

 

So if you need to load the full table anyway, DAX would probably be faster.

  • Jswedberg's avatar
    Jswedberg
    Regular Visitor
    8 years ago

    You can also wrap a DISTINCT(SELECTCOLUMNS(table, "col1name", Col1fromtable, table, "col2name", Col2fromtable)

    • ToFrai's avatar
      ToFrai
      Frequent Visitor
      8 years ago

      How can I copy distinct values from multiple columns into ONE single colum?

       

      The shown above always created multiple columns into which the code copies the distinct values from each column.

       

      Thanks!

      • ImkeF's avatar
        ImkeF
        Community Champion
        8 years ago

        This formula will create a list of distinct values in the query editor (where "Source" is the name of the table/previous step and column names in square brackets):

         

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

        Not sure if you want this as a standalone query or merge the resulting list with some other table?