SQL Statement Dynamic Date range

[Anonymous]

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.

[Anonymous · Accepted solution]

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

[kaldorr]

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