Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CroWorC
Frequent Visitor

Selecting from database with dynamic filter from table

Maybe there is some help and maybe it is not possible, but i do have a question.

I have a simple Excel table with some numbers in it (projectnumbers) 

 

IDProjectnumber
12345
25642
37863
49897

 

Every now and then a projectnumber is added to the list. 

The Excel file will be imported in PowerBi every time a change is made.

I also have a table in a database with manny records >10 Million.

I want to import only the records with a projectnumber from the Excel list using an advanced database connection.

Something like 

 

SELECT *

FROM databasetable

WHERE projectnumber IN (numbers from excel file)

 

Is it possible to put the projectnumbers from the excel file in the right place? So I don't have to import lots of unused data or make adjustments to the SQL statement ecery time a projectnumber is added?

The WHERE statement should look like

WHERE projectnumber IN(2345,5642,7863,9897)

 

Kind regards,

Harry Kraaij

 

 

 

3 REPLIES 3
HotChilli
Super User
Super User

There are several ways to do it but the important thing is to make the query fold (or prevent the Query folding from breaking)

 

One way to do this is to add a column 'Merged' to the Excel query like

"[projectnumber] = " & Text.From([ProjectNumber])   --  edit your query for correct fieldnames

and then add a step to the SQL query to build the SQL clause from the new column in the Excel table like:

#"Filtered Rows" = Table.SelectRows(dbo_FactInternetSales, Expression.Evaluate( "each " & Text.Combine(TableExcel[Merged], " or ")))

 

Your dbo_FactInternetSales will  have a different name.

Basically, you are constructing the sql clause to send back to the db - this might take some debugging to get the syntax right.  Right-click the Query steps and make sure 'View Native Query' is enabled.

Thanks for your answer.

 

The solution offerd is a solution in Excel. Making the sql statement in Excel works if you are a single user or the owner of the excel file.  In my case the projectteam is owner of the Excel file and not willing me to put in code. So every update will be an Excel file without code.  (and its automated so i dont see if a new version is published)

Maybe I should use a dataflow to prepare the data. he effect will be the same, a thin and swift report.

 

The solution offered is a powerbi solution.

All the steps take place in Power Query in Powerbi. You will have 2 queries there : the Excel query and the SQL query.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors