The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
34 | |
19 | |
19 | |
16 | |
13 |