Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
Hi @Anonymous,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
I first createsd a table "ORDERS"
Next, I right click on ORDERS and select "Reference"
Next I renamed the table to "INVENTORY" and grouped by "FRUIT"
Result:
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
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!
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
On the "Home" tab "Enter Data"
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.
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
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
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!
You will still get that option. Right click on the customn column you have created , you will see Remove duplicates option.
Thanks
Raj
@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
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?