Forum Discussion
Merging queries with partial common column
Greetings, I am looking for a way to make this conversion in PowerQuery
Please note that the only common value is column 1 and that the quantity of values is not the same in all columns
Thanks a lot
| 0 | Yes | 0 | Yes | Negative | F | Submitted | ||||
| 1 | Yes | 1 | Yes | Negative | F | Non-Submitted | ||||
| 2 | Yes | 2 | Yes | Positive | M | Submitted | ||||
| 3 | Yes | 3 | Yes | Positive | M | Pending | ||||
| 0 | Negative | 4 | Null | Submitted | ||||||
| 1 | Negative | 5 | Negative | Submitted | ||||||
| 2 | Positive | 6 | Positive | |||||||
| 3 | Positive | |||||||||
| 4 | Null | |||||||||
| 5 | Negative | |||||||||
| 6 | Positive | |||||||||
| 0 | F | |||||||||
| 1 | F | |||||||||
| 2 | M | |||||||||
| 3 | M | |||||||||
| 0 | Submitted | |||||||||
| 1 | Non-Submitted | |||||||||
| 2 | Submitted | |||||||||
| 3 | Pending | |||||||||
| 4 | Submitted | |||||||||
| 5 | Submitted |
Table.Group is generated automatically as here
After that in formula bar (or in Advanced Editor) you shall for each column remove this part
and replace each _ with formulas above
7 Replies
If you mean transform left part of the screenshot to right one as
you may query the range, replace empty values on null (I had space when copy/pasted, replace what you actually have), group by first column adding aggregation for each next column with All rows; in formula bar replace group aggregation by removing nulls and returning first value. Entire script
let Source = Excel.CurrentWorkbook(){[Name="Range"]}[Content], #"Replaced Value" = Table.ReplaceValue( Source," ",null, Replacer.ReplaceValue,{"Column2", "Column3", "Column4", "Column5"} ), #"Grouped Rows" = Table.Group( #"Replaced Value", {"Column1"}, { {"Column2", each List.First(_[Column2])}, {"Column3", each List.First( List.RemoveNulls( _[Column3]))}, {"Column4", each List.First( List.RemoveNulls( _[Column4]))}, {"Column5", each List.First( List.RemoveNulls( _[Column5]))} } ) in #"Grouped Rows"So there is no user friendly way to do the last step? I dont understand how to do that "Table.group(" step other than to replace values in your formula
Table.Group is generated automatically as here
After that in formula bar (or in Advanced Editor) you shall for each column remove this part
and replace each _ with formulas above
- Start Merge Tables.
Step 1: Select your main table.
Step 2: Pick your lookup table.
Step 3: Select matching columns.
Step 4: Choose the columns to update in your main table.
Step 5: Pick the columns to add to your main table.
Step 6: Choose additional merging options.
