Forum Discussion
Dynamic Data Refresh - Power BI Service
- Anonymous7 years ago
I have a similar stored procedure where i need to pass along a parameter for start and end dates that i'm calculating in a function.
So my source line looks like:
Source = Sql.Database("MyInstance.database.windows.net", "SQL-DW-PROD", [Query="Exec MyDataBase.[sp_MyProcedure] 0, " & fnStartDate & ", " & fnEndDate", HierarchicalNavigation=true, CommandTimeout=#duration(0, 0, 90, 0)])Then i have another 2 queries as the functions, named fnStartDate and fnEndDate where i can calculate those exact dates. They can look as based as this:
let Result = "'2016-01-01'" in ResultOr you can get more complex and calculate a date like this:
let CurrentMonth = Date.Month(DateTime.LocalNow()), CurrentYear = Date.Year(DateTime.LocalNow()), Result = Number.ToText(CurrentYear) & "-" & Number.ToText(CurrentMonth) & "-03" in Result
2 Replies
- AnonymousNot applicable
I have a similar stored procedure where i need to pass along a parameter for start and end dates that i'm calculating in a function.
So my source line looks like:
Source = Sql.Database("MyInstance.database.windows.net", "SQL-DW-PROD", [Query="Exec MyDataBase.[sp_MyProcedure] 0, " & fnStartDate & ", " & fnEndDate", HierarchicalNavigation=true, CommandTimeout=#duration(0, 0, 90, 0)])Then i have another 2 queries as the functions, named fnStartDate and fnEndDate where i can calculate those exact dates. They can look as based as this:
let Result = "'2016-01-01'" in ResultOr you can get more complex and calculate a date like this:
let CurrentMonth = Date.Month(DateTime.LocalNow()), CurrentYear = Date.Year(DateTime.LocalNow()), Result = Number.ToText(CurrentYear) & "-" & Number.ToText(CurrentMonth) & "-03" in Result- AnonymousNot applicable
Something to add to this response, depending on how picky your stored procedure is for the date you pass, you could format the date more or change it to a Date Type. I'm concerned with things like whether your stored procedure needs a 2 digit month.