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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi ImkeF 

 

I followed your approach in this thread to create my first manual query in Power BI.  I wanted to create this list of distinct values from two different columns from two tables, so that I could use the newly created column as the relational data point between those two tables/columns.  However, I'm getting the ambiguity error when trying to relate the second column to it.  Is that not the purpose of creating this union list?  I also tried creating a table that did the same thing, and the ambiguity error popped up there as well.

 

Is there a way to create a stand alone column that is the list of unique values from two columns for the purpose of relating those two columns in one-to-many relationships?

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    Hi Anonymous 

    sorry, but I cannot follow.

    Could you possibly post some sample data?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      ImkeF 

       

       = List.Distinct(List.Union({#"Obligations by FY"[Project Number],#"CJI3 Actual Cost Data"[Project Number]}))

       

      So, that's the query I created.  In my model, I then try to create a relationship between this new column, and the two different "Project Number" columns, so that the new "Project Number" column can be used for filtering purposes in visuals.

       

      I can create one relationship, but upon creating the second relationship, I get the error about ambiguous paths.  So, I can't do what I wanted with the new query/column. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I guess I'll post a new question.  Was just trying to figure out a quick way to make the "one" side of my relationships for two tables that is dynamic, vs. manually joining them into a table to bring in on my own and having to manually refresh that table with new unique values all the time.  Creating the table within Power BI by joining the unique values from the two tables seemed like the easiest way to do it, which landed me here.