filter a table with 800 million rows by a list of values

[Rebecca6634]

I have a SQL table (table1) with 800 million rows imported into power bi. One column is called 'clinicalcode'. I have a second table (table2)  imported from an excel document which has 1000 rows and one column called 'code'. The list of codes in the excel document frequently changes.

I want to only keep rows from table1 where 'clinicalcode' matches a value in 'code' from table2 in the most efficient way possible.

using a right outer join and list.contains are taking a long time to process. 

I can't using query folding as one source is in excel and I'm working in Desktop so I don't think I can use the new Power Bi Online list parameter.

What's the best way to solve this please?

[amustafa · Accepted solution]

Best option is to upload your clinical codes to your SQL server then run a join locally at server level to get subset of 800 mil rows.

OR...

Create a list in Power Query from your  'clinicalcode' and update your M Code as following. replace the list name and datasoure accordingly. 

let
    // Assuming StoreKeyList is a query or list defined elsewhere
    StoreKeyList = StoreKeyList,  // Ensure this is a reference to your existing list or query

    // Convert StoreKeyList to a comma-separated string for SQL query
    StoreKeyString = Text.Combine(List.Transform(StoreKeyList, each Text.From(_)), ","),

    // SQL query with WHERE clause to filter based on StoreKey
    SqlQuery = "SELECT * FROM dbo.FactInventory WHERE StoreKey IN (" & StoreKeyString & ")",

    // Execute the SQL query
    Source = Sql.Database("localhost", "ContosoRetailDW", [Query=SqlQuery])
in
    Source

[Rebecca6634]

Thank you! This worked a treat!

[aallman]

How can I create my list of values using the values in a single column from a separate query?

[amustafa]

See this video on how to create a list from a table.

The Magic of working with Lists in Power Query (youtube.com)