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.
This problem has been driving me round the bend for the last few days. The client for whom I'm creating reports do not have access to their main database and so far receive their data in tabula form in Excel. I want to apply parameters when importing data for Power BI reporting but have so far been unsuccessful in this as I can't use any sql queries or stored procedures.
I am able to create the parameters but cannot apply them on data import. Any attempt to do so by following the steps of applying the parameter in the Data Source dialogue as outlined in the below article returns errors.
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates
Solved! Go to Solution.
You could use ODBC.Query() to bring in Excel data. Though it's rather limited in what functions can be used.
Ex:
Connection string
dbq=\\server\FOLDER\samplefile.xlsx;defaultdir=\\server\FOLDER;driverid=1046;maxbuffersize=2048;MaxScanRows=1;pagetimeout=5;dsn=Excel Files
Then you can use something like...
Select t1.* From [Sheet1$] as t1 Where t1.Column1 >= '20'
Then in advanced editor, replace '20' with parameter value.
If that isn't possible, you can always use parameter to filter the data brought in.
This problem has been driving me round the bend for the last few days. The client for whom I'm creating reports do not have access to their main database and so far receive their data in tabula form in Excel. I want to apply parameters when importing data for Power BI reporting but have so far been unsuccessful in this as I can't use any sql queries or stored procedures.
I am able to create the parameters but cannot apply them on data import. Any attempt to do so by following the steps of applying the parameter in the Data Source dialogue as outlined in the below article returns errors.
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/
You could use ODBC.Query() to bring in Excel data. Though it's rather limited in what functions can be used.
Ex:
Connection string
dbq=\\server\FOLDER\samplefile.xlsx;defaultdir=\\server\FOLDER;driverid=1046;maxbuffersize=2048;MaxScanRows=1;pagetimeout=5;dsn=Excel Files
Then you can use something like...
Select t1.* From [Sheet1$] as t1 Where t1.Column1 >= '20'
Then in advanced editor, replace '20' with parameter value.
If that isn't possible, you can always use parameter to filter the data brought in.
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.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |