Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
3 | 7863 |
4 | 9897 |
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.