Forum Discussion
Merge queries. How replace a row?
If my understanding of the problem is correct, this would probably be the easiest option:
1) Append the long table to your table that holds the replacements
2) Check the index column and remove duplicates
Removing duplicates will keep the first row it finds in a table. This should also be fast.
You can use a Left Anti to get a data set from the first list that doesnt have rows in the 2nd then append the 2nd.
You could also create a function to replace values, but I suspect this will be very slow on a large dataset.
https://msdn.microsoft.com/en-us/query-bi/m/table-replacevalue
This is a useful blog from cwebb that uses Table.AddKey to improve merge performance.
https://blog.crossjoin.co.uk/2018/03/16/improving-the-performance-of-aggregation-after-a-merge-in-power-bi-and-excel-power-query-gettransform/
https://msdn.microsoft.com/query-bi/m/table-addkey
If my understanding of the problem is correct, this would probably be the easiest option:
1) Append the long table to your table that holds the replacements
2) Check the index column and remove duplicates
Removing duplicates will keep the first row it finds in a table. This should also be fast.