Merge first or remove duplicates (query folding)

Hi akhaliq7 ,

There's quite a lot to unpack with a question like this. A lot of it come down to 'it depends'.

First and foremost, if you're working on a foldable data source (SQL Server, for example) then your real focus needs to be on making sure that query folding is maintained, rather than in what order it happens. On a fully folded query, Power BI will optimise the order of operations behind the scenes when it generates the native query to go to the source.

If you're not working on a foldable source, then you would need to evaluate your question based on your specific scenario. For example, if you have thousands of duplicated records in your data then, predictably, you would likely see performance gains in the merge process by reducing these rows first. However, if your merge operation introduces duplicates through crossjoins, then you'd possibly see overall performance gains by only performing the remove duplicates step once, after the merge has completed.

If you really want to improve your merge performance then don't do them at all - load both tables to the data model and relate them. Let VertiPaq/AS Tabular/DAX do the work.

Pete