cancel
Showing results for 
Search instead for 
Did you mean: 
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 II
Super User II

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors