Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Reference Table through Power BI - PLEASE HELP!

I am trying to build a relationship between two tables.  One of the tables was supposed to have unique values, to create a relationship.

 

Is there a way in PowerBI to take a column from a table, create a third table from the deisred column(the reference table) and distill the values down to a unique set of values?

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous

     

    You can easily do this in Power Query.

     

    Right click Original Table, select Reference , Remove the unwanted columns, select "Remove Duplicates", now you will have the column with unique values .

     

    You can use this table as Relationship / Bridge table as you needed.

     

    Thanks

    Raj

14 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous

     

    You can easily do this in Power Query.

     

    Right click Original Table, select Reference , Remove the unwanted columns, select "Remove Duplicates", now you will have the column with unique values .

     

    You can use this table as Relationship / Bridge table as you needed.

     

    Thanks

    Raj

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous

       

      Thanks, one last question.  To create the relationships, I am concatenating two colums.  Since this is a function, I can't seem to remove the duplicates in Power Query.  Any thoughts?

       

      Thanks!

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        You will still get that option. Right click on the customn column you have created , you will see Remove duplicates option.

         

        Thanks
        Raj

  • cb_ohio's avatar
    cb_ohio
    Frequent Visitor
    7 years ago

    One quick and easy method would be to make a reference copy of one of the tables and group by the column you want distinct values for.  You can eliminate any of the other columns you don't want.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      cb_ohio

       

      Can you send me some screenshots or steps...not sure I know how to do this.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Yes, you can do that way also, Both gives same result.

         

        Click on the column you created, Go to Transform Tab -> Click Group by , it will create 2 columns , one is the unique value of your column and another one is based on your selection ( ex: count). You can delete the extra column and keep the custom column.

         

        Thanks
        Raj

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    A solution using DAX could be like below. Please give it a try.

    Table =
FILTER (
    DISTINCT (
        SELECTCOLUMNS ( 'DimProduct', "key", CONCATENATE ( [BrandName], [ColorName] ) )
    ),
    ISBLANK ( [key] ) = FALSE ()
)

    Best Regards,

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-jiascu-msft

       

      What is the first step in creating a new table.  It is no longer in the "Modeling" tab?

       

      Can you also breakdown the components in the DAX starement so I understand the references and how to translate that to my dataset and how they apply?

       

      Thanks!

  • cb_ohio's avatar
    cb_ohio
    Frequent Visitor
    7 years ago

    I first createsd a table "ORDERS"

    ORDERS

    Next, I right click on ORDERS and select "Reference"

    Reference Table 

    Next I renamed the table to "INVENTORY" and grouped by "FRUIT"

     

     

     

    Result:

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    Could you please mark the proper answers as solutions?

    Best Regards,
    Dale