Upsert Query in power query

[jaryszek]

Hi Guys,

i am looking for equivalent for upsert query like here:

https://www.access-programmers.co.uk/forums/threads/append-if-rows-do-not-exists-else-update-if-exis... 

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

[HotChilli · Accepted solution]

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.

[jaryszek]

Thank you so much for tipping me,

Best,
Jacek

[jaryszek]

What 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

[jaryszek]

thanks HotChilli. 

what you mean by column of color ? 

Best,
Jacek

[HotChilli]

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