Adding a column from one table to another

[RichOB]

Hi, I have 2 tables, how can I add the Property_Type from Table into Table1 please?

Table1

Location	Property_ID	Bed_ID
Brighton	Prop1	Bed1
Brighton	Prop2	Bed2
Brighton	Prop3	Bed3
Southampton	Prop4	Bed4
Southampton	Prop5	Bed5
Southampton	Prop6	Bed6
Southampton	Prop7	Bed7

Table2

Bed_ID	Property Type
Bed1	Garden View
Bed2	Garden view
Bed3	Sea View
Bed4	Sea View
Bed5	Sea View
Bed6	Garden view
Bed7	Garden view

The Table I need

Location	Property_ID	Bed_ID	Property Type
Brighton	Prop1	Bed1	Garden View
Brighton	Prop2	Bed2	Garden view
Brighton	Prop3	Bed3	Sea View
Southampton	Prop4	Bed4	Sea View
Southampton	Prop5	Bed5	Sea View
Southampton	Prop6	Bed6	Garden view
Southampton	Prop7	Bed7	Garden view

Thanks

[danextian · Accepted solution]

Hi @RichOB 

If there's a one-to-many relationship from Table2 to Table1 and the latter is on the many side, you can create this calculated column

RELATED ( Table2[Property_Type] )

If there isn't, you can use LOOKUPVALUE

LOOKUPVALUE ( Table2[Property_Type], Table2[Bed_ID], Table1[Bed_ID] )

LOOKVALUE will return an error if there are more than one results for the same Bed_ID. You can add more search criteria. Here's the syntax

LOOKUPVALUE (
    <result_columnName>,
    <search_columnName>,
    <search_value>
    [, <search2_columnName>, <search2_value>]…
    [, <alternateResult>]
)

[Visharavana · Accepted solution]

Hi @RichOB 

You can achieve this by two methods. Power Query or DAX
Power Query:
Just merge those two tables using the Bed_ID column.
DAX:
Create a relationship between two tables and use a RELATED function.
Property Type = RELATED(Table2[Property Type])

[V-yubandi-msft]

Hi @RichOB ,

Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.

[v-hashadapu]

Hi @RichOB , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[V-yubandi-msft]

Hi @RichOB ,

Could you let us know if your issue has been resolved or if you are still experiencing difficulties? Your feedback is valuable to the community and can help others facing similar problems.

Thank You.

[V-yubandi-msft · Accepted solution]

Hi @RichOB ,

Thank you for reaching out to the Microsoft Fabric Community. I tested your scenario and was able to bring the Property Type from Table2 into Table1 using Bed_ID as the key. In Power BI’s Power Query, you can do this by.

1. Opening the Power Query Editor.
2. Selecting Table1 and choosing Home > Merge Queries as New.
3. In the merge dialog, selecting Table1[Bed_ID] and Table2[Bed_ID] as the join columns, and using a Left Outer Join.
4. After merging, expanding the new column and choosing only the Property Type field.
5. Selecting Close & Apply to update your data model.

FYI:

Thank you for your responses @Visharavana , @danextian .

I’ve attached the PBIX file, which includes these steps so you can review the process. If this resolves your issue, you can mark it as the Accepted Solution to assist others.

[Visharavana · Accepted solution]

Hi @RichOB 

You can achieve this by two methods. Power Query or DAX
Power Query:
Just merge those two tables using the Bed_ID column.
DAX:
Create a relationship between two tables and use a RELATED function.
Property Type = RELATED(Table2[Property Type])

[danextian · Accepted solution]

Hi @RichOB 

If there's a one-to-many relationship from Table2 to Table1 and the latter is on the many side, you can create this calculated column

RELATED ( Table2[Property_Type] )

If there isn't, you can use LOOKUPVALUE

LOOKUPVALUE ( Table2[Property_Type], Table2[Bed_ID], Table1[Bed_ID] )

LOOKVALUE will return an error if there are more than one results for the same Bed_ID. You can add more search criteria. Here's the syntax

LOOKUPVALUE (
    <result_columnName>,
    <search_columnName>,
    <search_value>
    [, <search2_columnName>, <search2_value>]…
    [, <alternateResult>]
)