Dynamic Parameter, Table Name

[wuyuansheng]

let
pvDate =
if Date.DayOfWeek(PriceDate) = 0 then
Date.AddDays(PriceDate, -6)
else if Date.DayOfWeek(PriceDate) = 1 then
Date.AddDays(PriceDate, -6)
else if Date.DayOfWeek(PriceDate) = 2 then
Date.AddDays(PriceDate, -6)
else if Date.DayofWeek(PriceDate) = 3 then
Date.AddDays(PriceDate, -6)
else
Date.AddDays(PriceDate, -4),

yyyyMM = DateTime.ToText(pvDate, "yyyyMM"),
yyyyMMdd = DateTime.ToText(pvDate, "yyyy-MM-dd"),
Source = Sql.Database("localhost", "CSWebChecksWebsite", [Query="Select axdgn202305.ClientFTP As [Client FTP], PriceValue From axdgn202305, AxiomClients where AxiomClients.clientFTP = axdgn202305.ClientFTP and PriceDate = '20230515' and PriceSource = 'priced' and AxiomClients.checksEnabled = 1"])
in
Source

I have the above query in direct query mode which is working fine. The date is fixed, once I change to the following code, it would complain of error. PriceDate is a parameter which I intend to display a date slicer for user to pick a date. Something todo once this is fixed.

Source = Sql.Database("localhost", "CSWEBChecksWebsite", [Query="Select LOWER(axdgn" & yyyyMM & ".ClientFTP) As [Client FTP], PriceValue From axdgn" & yyyyMM & ", AxiomClients where AxiomClients.clientFTP = axdgn" & yyyyMM & ".ClientFTP and PriceDate = '" & yyyyMMdd & "' and PriceSource = 'priced' and AxiomClients.checksEnabled = 1"])

 The data would load fine, but the apply step yields the following error. Any idea how to resolve the issue?

[amitchandak · Accepted solution]

@wuyuansheng , check dynamic M parameters

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

https://www.youtube.com/watch?v=ilTeVH2Cwck

https://www.youtube.com/watch?v=L-wqwzj0-Xo