Query parameters when data source is excel workbook

[Richard3]

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

[Richard3]

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/

[Chihiro · Accepted 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.