Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
Thank you! This worked a treat!
How can I create my list of values using the values in a single column from a separate query?
See this video on how to create a list from a table.
The Magic of working with Lists in Power Query (youtube.com)
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.