Up until today I have been able to use a function to define the parameters of a Stored Procedure call that I'm making on an Azure SQL database. I have done quite a lot of testing to conclusively narrow down that the problem is dynamically assigning the parameter to this query.
If i use a dynamic parameter in my Power Query code, Power BI service will prevent me from refreshing. This worked all the way up until yesterday (8th April 2018).
Here is a forum post that I have created which provides a number of details:
https://community.powerbi.com/t5/Service/Has-the-ability-to-refresh-a-dataset-with-a-dynamic-SQL-sto...
The following code will not allow refreshes: (where fnStartDate and fnEndDate are functions to dynamically return dates)
Source = Sql.Database("Example042.database.windows.net", "Example-DB", [Query="Exec APAC_Example.[sp_Example] 0, " & fnStartDate & ", " & fnEndDate & ", 'EP231%'", HierarchicalNavigation=true, CommandTimeout=#duration(0, 0, 90, 0)]),The following code will allow refreshes:
Source = Sql.Database("Example042.database.windows.net", "Example-DB", [Query="Exec APAC_Example.[sp_Example] 0, '2016-01-01', '2019-01-01', 'EP231%'", HierarchicalNavigation=true, CommandTimeout=#duration(0, 0, 90, 0)]),
v-xiaoyan-msft
on:
Power BI Embedded with DirectLake Dataset Error
on:
Unable to create new dataflows Power BI GCC tenant
on:
BUG::SLICER::IDbConnection interface