Forum Discussion
CroWorC
5 years agoFrequent 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) ID Projectnumber 1 2345 2 5642 ...
HotChilli
5 years agoCommunity Champion
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.