topic Re: How to Join tables and Add column using DAX(Edit Query) in DAX Commands and Tips

How to Join tables and Add column using DAX(Edit Query)

Anonymous — Wed, 19 Aug 2020 15:32:22 GMT

I have to join two tables A and B using ID.
Then add on column 'Degree' from table B to Table A
Expected O/P : Table contains column A,B & C

Input File:

Table A
ID	Name
111	Adex
222	belly
333	Caty

Table B
ID	Degree
111	B.Com
222	B.Tech
333	BCA

Expected Ouput :

Table A
ID	Name	Degree
111	Adex	B.Com
222	belly	B.Tech
333	Caty	BCA

Using DAX (Edit query/Transform Data)
Please help me out solving this and if possible please explain syntax to be used.

Thanks in Advance!

Re: How to Join tables and Add column using DAX(Edit Query)

Anonymous — Wed, 19 Aug 2020 15:52:28 GMT

Providing you have a relationship, just add a column on Table A, the paste in the following

Degree = RELATED('Table B'[Degree])

Re: How to Join tables and Add column using DAX(Edit Query)

amitchandak — Wed, 19 Aug 2020 15:53:20 GMT

Anonymous , one is merge table in edit query

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Dax way

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

New column in Table A

maxx(filter('Table B', 'Table A'[ID] ='Table B'[User ID]),'Table B'[Degree])

Re: How to Join tables and Add column using DAX(Edit Query)

vivran22 — Thu, 20 Aug 2020 03:50:21 GMT

Hello Anonymous

Power Query way: Merge Queries

With Table A selected > Home > Merge Queries:

Select Table B in the second table and select the common column (ID in this case):

Under Join Kind, select Left Outer. Click Ok

Expand the table > Select the required column from Table B

Click Ok.

Output:

For more details, you may refer to the following:

https://www.vivran.in/post/bi-simplified-webinar-1-p4-merge-queries

https://www.vivran.in/post/vlookup-joins-using-power-query

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

Re: How to Join tables and Add column using DAX(Edit Query)

vivran22 — Thu, 20 Aug 2020 03:53:08 GMT

Anonymous

DAX way:

LOOKUPVALUE (when there is no relationship between tables)

Add a new column using following DAX

Degree = LOOKUPVALUE('Table B'[Degree],'Table B'[ID],'Table A'[ID])

RELATED (When the tables are related)

Degree 2 = RELATED('Table B'[Degree])

https://www.vivran.in/post/dax-lookupvalue

https://www.vivran.in/post/dax-data-modeling-with-related-relatedtable

Re: How to Join tables and Add column using DAX(Edit Query)

pearlking — Sun, 15 Jan 2023 11:13:57 GMT

What should we do if those tables have many to many relationships?