Forum Discussion
Upsert Query in power query
Hi Guys,
i am looking for equivalent for upsert query like here:
Ok user has table here - TableInput:
| ID | Animal | Color |
| 1 | Dog | Green |
| 2 | Cat | White |
and in core model i have tableToCompare:
| ID | Animal | Color |
| 1 | Dog | Green |
| 2 | Cat | Black |
| 3 | Mouse | Red |
But what i want to get is:
| ID | Animal | Color |
| 1 | Dog | Green |
| 2 | Cat | White |
| 3 | Mouse | Red |
so row with ID = 3 was inserted into InputTable because does not exists in tableToInput.
And Cat which was updated in TableToInput by user to White i am keeping. So this row has been updated from black (TableToCompare) to white (TableInput).
How to do this kind of operation in power query? (not power BI)
Please help
Best,
Jacek
Create one custom column with the logic to select the "right" Color.
Fix the datatypes on the columns. You don't need to return the ID and Animal if they don't change.
If it works, it works.
5 Replies
Create one custom column with the logic to select the "right" Color.
Fix the datatypes on the columns. You don't need to return the ID and Animal if they don't change.
If it works, it works.
Thank you so much for tipping me,
Best,
Jacek
you could start from tableToCompare and perform a merge (left outer) with TableInput on ID field.
You could then decide which column of color you prefer
thanks HotChilli.
what you mean by column of color ?
Best,
JacekWhat i did as your suggestion is:
let Source = Excel.CurrentWorkbook(){[Name="TableToCompare"]}[Content], #"Merged Queries" = Table.NestedJoin(Source, {"ID"}, TableInput, {"ID"}, "TableInput", JoinKind.LeftOuter), #"Expanded TableInput" = Table.ExpandTableColumn(#"Merged Queries", "TableInput", {"ID", "Animal", "Color"}, {"TableInput.ID", "TableInput.Animal", "TableInput.Color"}) in #"Expanded TableInput"and what i got:
And now just create custom columns and check if they are the same?
No better way?Best,
Jacek
