Forum Discussion
Full Outer Join Handling Null Values in common fields
Hello Team,
I do a full outer join , results in null values in some of the columns.
I would like to have a single column with all values for all the common columns.
What is the best approach?
| A | B | C | D |
| 1 | 100 | 200 | 500 |
| 2 | 101 | 201 | 501 |
| 3 | 102 | 202 | 502 |
| 4 | 103 | 203 | 503 |
| A | B | C | E |
| 1 | 100 | 200 | 600 |
| 2 | 101 | 201 | 601 |
| 3 | 102 | 202 | 602 |
| 5 | 104 | 204 | 604 |
Load both tables into Power BI and open the Power Query Editor.
Perform a full outer join using "Merge Queries" on your key column (Column A).
Expand the merged column to include all relevant columns (B, C, D, E).
Add a custom column using an if statement to consolidate values. For exampleif [D] <> null then [D] else [E]
Remove the original D and E columns as needed.
Click “Close & Apply” to load the transformed data.💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
5 Replies
Hello ArvindJha
Please clarify the desired outcome you're seeking. Providing sample data or a screenshot of the expected result would be beneficial.
You mentioned attempting a full-outer join, which typically yields multiple columns, yet you require a single column containing all values. This is somewhat confusing; could you please elaborate on your issue?
Thanks,
Udit
Thanks for the response , Expected Outcome
A B C D E 1 100 200 500 600 2 101 201 501 601 3 102 202 502 602 4 103 203 503 5 104 204 604 Load both tables into Power BI and open the Power Query Editor.
Perform a full outer join using "Merge Queries" on your key column (Column A).
Expand the merged column to include all relevant columns (B, C, D, E).
Add a custom column using an if statement to consolidate values. For exampleif [D] <> null then [D] else [E]
Remove the original D and E columns as needed.
Click “Close & Apply” to load the transformed data.💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
