Reference Table through Power BI - PLEASE HELP!

[Anonymous]

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?

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,
Dale

[cb_ohio]

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]

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]

@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!

[v-jiascu-msft]

Hi @Anonymous,

Please refer to the snapshot and the comments on the code.

Table =
FILTER (  //filter out blanks, for you want to establish relationship.
    DISTINCT (  //only keep unique values.
        SELECTCOLUMNS ( 'DimProduct', "key", CONCATENATE ( [BrandName], [ColorName] ) )
    ),  //concatenate two fileds as you mentioned. 'DimProduct' is a table while [BrandName] and [ColorName] are columns of 'DimPorduct'.
    ISBLANK ( [key] ) = FALSE ()
)

Best Regards,

Dale

[cb_ohio]

On the "Home" tab "Enter Data"

[cb_ohio]

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]

@cb_ohio

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

[Anonymous]

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

[Anonymous · Accepted solution]

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]

@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]

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

Thanks
Raj

[Anonymous]

---

@Anonymous wrote:

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

Thanks
Raj

---

Did you try this?

[Anonymous]

@Anonymous

There doesn't seem to be the option to remove duplicates as the column I created using the CONCATENATE function is not showing in Query Editor?