Forum Discussion
Merge queries with common columns and unique values in each query
Hi, I need help with merging queries. Each query has 5 common columns, and a number of unique columns.
Each query has some unique rows based on the 5 common columns.
I've merged using full outer to show all unique rows, but I want to see all the common column data in the same column.
As you can imagine, the common column data is split up into seperate columns.
Is there a solution to this?
1 Reply
spinfuzerSolution Sage
Assuming the common columns have the same exact name, you could first do a left outer join to get rows unique to left table and rows in common with both tables. Then do a separate anti join on the right table to get rows only in the right table and Table.Combine (append) those two results together. You may have to create duplicates of the queries to achieve this.
(Left Only Rows + Common Rows) appended/combined with (Right Only Rows).
Otherwise you can just Table.ReplaceValue 5 times on the common columns.
Table.ReplaceValue(priorStep, each [column name], each [column name] ?? [column name from table 2], Replacer.ReplaceValue,{"column name"})