Forum Discussion
Selecting from database with dynamic filter from table
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.
