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

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?

  • ToFrai's avatar
    ToFrai
    Frequent Visitor
    8 years ago

    Hi Imke,

    thanks for the hint - where do I enter the code in the querie editor? Do I have to create a new table first, or add a new step in an existing query?

     

    Problem is that the querie editor shows source tables only, not the ones I caclulated.

    Isn't there a Dax formula that I can use in a new table that says something like "copy all (distinct) values from Table A Column 1, and then below all (distinct) valies from Table 2 Column 1, ...."?

     

    BR | Tobias

    • ImkeF's avatar
      ImkeF
      Community Champion
      8 years ago

      You CAN do it in your current query:

      Add step manually by clicking the fx-sign:

       

       

      But of course, this only works for tables that exist in the query editor.

      If you've created them in the data view with DAX, then you won't have access to them there.

       

      You might consider opening a new thread and specifically request a DAX-solution to enhance visibility of your request.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi ,

     

    I'm trying this M code and getting an error "Expression.SyntaxError: Invalid identiffier"

     

    Here is the code I'm entering to try and combine sales order number lists from two separate queries:

     

    = list.distinct(list.union({"ZSD026 - Sales Analysis Report"[#"Sales Order No"],"ZSD029 - Invoice Report Material Level"[#"Sales Order No"}))

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi, 

    I'm trying this solution to combine two columns of sales order numbers from two separate queries and get an error:

    "Expression.SyntaxError: Invalid identifier"

     

    here is my code:

    = list.distinct(list.union({"ZSD026 - Sales Analysis Report"[#"Sales Order No"],"ZSD029 - Invoice Report Material Level"[#"Sales Order No"}))

    • ImkeF's avatar
      ImkeF
      Community Champion
      7 years ago

      Hi Anonymous ,

      the M-language is case sensitive.