Forum Discussion
SQL Statement Dynamic Date range
Hi all,
I need to make the EndDate and StartDate respectively, in the following line a dynamic date range.
Sql.Database("Server", "DB", [Query= "exec dbo.WhatEver 'yyyy-mm-dd', 'yyyy-mm-dd'"])
The above Mcode line was created by a SQL statement, exec dbo.WhatEver 'yyyy-mm-dd', 'yyyy-mm-dd'. As far as I know, I cannot use a where clause in an exec statement. How will one call this date range dynamically?
I have tried creating dataparameters in power query and then combining it in a text line with the exec statement but you cannot use such a text line for the Query.
HI Anonymous - I believe you need to use an Open Rowset query to run a SQL stored proc. Please read the following blog by Teo Lachev: Power BI Dynamic M Query Parameters Reloaded – Prologika
2 Replies
HI Anonymous - I believe you need to use an Open Rowset query to run a SQL stored proc. Please read the following blog by Teo Lachev: Power BI Dynamic M Query Parameters Reloaded – Prologika
Hello Daryl,
I actually solved it by doing to following,
Start = Date.ToText(DateStart, "yyyy-MM-dd"),
End = Date.ToText(DateEnd, "yyyy-MM-dd"),
Query = "execute statement" & " " & "'" & End & "'" & ", " & "'" & Start & "'",Source = Sql.Database("Server name", "Database", [Query= Query])
Where the DateEnd and DateStart were determined as follows
Date.AddDays(Date.EndOfWeek(Date.From(DateTime.LocalNow())), -8)
Date.AddDays(Date.EndOfWeek(Date.From(DateTime.LocalNow())), -15)
Respectively
