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.
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:
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)]),
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.