Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Providing you have a relationship, just add a column on Table A, the paste in the following
Degree = RELATED('Table B'[Degree])
@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])
@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])
Related article:
https://www.vivran.in/post/dax-lookupvalue
https://www.vivran.in/post/dax-data-modeling-with-related-relatedtable
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
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
@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])
Providing you have a relationship, just add a column on Table A, the paste in the following
Degree = RELATED('Table B'[Degree])
What should we do if those tables have many to many relationships?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
27 | |
21 | |
12 | |
8 |
User | Count |
---|---|
74 | |
52 | |
45 | |
15 | |
12 |