Use Merge or List. Contains: Best option?

[filiparibeiro]

Hello.

I have 2 tables in power query. Each of these tables has a column with ID numbers. 
I want to check if the IDs in the 1st table appear in the 2nd table.
In order to do this, which would you consider to be the best option and why: merge tables or use List.contains? 

Thanks in advance. 

[BA_Pete · Accepted solution]

Hi @filiparibeiro ,

I think this depends on where your two tables come from and how much flexibility you need on the output format/structure.

If they're both from the same SQL Server DB (or other foldable source) and you need to view the output in a number of different ways then I'd recommend merging, providing you keep query folding intact. I'd recommend this as, depending on what you're trying to match and how you want the results to be output, the different merge types (LEFT OUTER, FULL OUTER etc.) give you the most flexibility over this output.

If one is from an SQL Server DB (or other foldable source) and the other any other source, but you just need to see a basic list of items from one table that are/are not in the other, then I'd recommend using List.Contains on a buffered list (from the non-foldable source), providing you keep query folding intact on the foldabe source. I'd recommend this as using this method will stream the buffered list to the foldable source in the WHERE clause so can crunch massive tables super-fast, but at the expense of being limited on the type of output you can achieve at these high speeds.

If neither source is foldable, I'd probably go for List.Contains with a buffered list. This will only dump your match list into memory, rather than scanning an entire table for the merge.

Pete

[filiparibeiro]

Hi @BA_Pete.

Thank you for your reply.

Could you tell me in which step you apply the list.buffer? 
Do you apply it to the 2nd table column before adding columns with list.contains?

Thanks. 

[BA_Pete · Accepted solution]

Ok, so, let's say you have TableA with a list of ID's and you want to check if they are in TableB. You would create the following step in TableB to filter it for only ID's that are in TableA:

Table.SelectRows(
    previousStepName,
    each List.Contains(
        List.Buffer(TableA[ID]),
        TableB[ID]
    )
)

Non-foldable TableB: This puts the TableA[ID] list into memory before checking against TableB to prevent multiple scans of TableA

Foldable TableB: This streams the TableA[ID] list into the WHERE clause of the TableB native query to pass the work back to the source.

Pete

[BA_Pete · Accepted solution]

Hi @filiparibeiro ,

I think this depends on where your two tables come from and how much flexibility you need on the output format/structure.

If they're both from the same SQL Server DB (or other foldable source) and you need to view the output in a number of different ways then I'd recommend merging, providing you keep query folding intact. I'd recommend this as, depending on what you're trying to match and how you want the results to be output, the different merge types (LEFT OUTER, FULL OUTER etc.) give you the most flexibility over this output.

If one is from an SQL Server DB (or other foldable source) and the other any other source, but you just need to see a basic list of items from one table that are/are not in the other, then I'd recommend using List.Contains on a buffered list (from the non-foldable source), providing you keep query folding intact on the foldabe source. I'd recommend this as using this method will stream the buffered list to the foldable source in the WHERE clause so can crunch massive tables super-fast, but at the expense of being limited on the type of output you can achieve at these high speeds.

If neither source is foldable, I'd probably go for List.Contains with a buffered list. This will only dump your match list into memory, rather than scanning an entire table for the merge.

Pete