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 two tables tblcalls and tblcallscc which stand in a 1:1 relationship via tblcalls.call_id and tblcallscc.cc_call_id. I want to merge them to one big table. Howevery, sometimes records are missing in one of the tables. And I also want to have a new column "id" that holds either tblcalls.call_id or tblcallscc.cc_call_id of whatever record exists.
Example:
This is what I would do in SQL to achieve this:
SELECT call_id as "id", * FROM tblcalls LEFT JOIN tblcallscc ON call_id = cc_call_id
UNION
SELECT cc_call_id as "id", * FROM tblcalls RIGHT JOIN tblcallscc ON call_id = cc_call_id ORDER BY i
Now in Power BI I managed to do the UNION via "Merge Queries ->Merge Queries as New" and use a FULL OUTER JOIN. However, I am a bit lost how to efficiently add the "id" column.
Edit: I actually just found a way. What I did was to add a Conditional Column, but I would like to know if this is the way to do it, of if there is a better way.
Solved! Go to Solution.
then I don't see any better method other than yours.
Go for full outer join
Refer the below link for more details.
http://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query
Sorry, I missed a word in my Question. I did use the FULL OUTER JOIN. My question was, if the way I added the "id" column could be improved.
then I don't see any better method other than yours.
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |