NativeQuery with query parameter appear error when load to report

[HelloTheSun]

I create M-query code with "Value.NativeQuery" function which get input from query parameters.

after that I try to refresh in power query, there are not any error.

but when I load data to report then error is appear that must declare the variables?

My code

Error

please help me to solve this problem.

Thank.

[Anonymous]

Hi @HelloTheSun ,

Please try:

let
  //Connection
  Source = Sql.Database("10.16.89.71", "DWH"), 
  //Parameter
  _date_start = Date.ToText(
    #date(Date.Year(DateStart), Date.Month(DateStart), Date.Day(DateStart)), 
    "yyyy-MM-dd", 
    null
  ), 
  _date_end = Date.ToText(
    #date(Date.Year(DateEnd), Date.Month(DateEnd), Date.Day(DateEnd)), 
    "yyyy-MM-dd", 
    null
  ), 
  _pol_status = PolStat_Param, 
  //Native SQL
  _function = Value.NativeQuery(
    Source, 
    "SELECT * FROM [dbo].[Fn_TAProductionReport](@date_start,@date_end) 
        WHERE [PolicyStatus] = @pol_status", 
    [date_start = @_date_start, date_end = @_date_end, pol_status = @_pol_status], 
    [EnableFolding = true]
  ), 
  output = _function
in
  output

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[HelloTheSun]

Thank for reply

I try but it still the same error when load to report.

[Anonymous]

Hi @HelloTheSun ,

How about this:

let
  //Connection
  Source = Sql.Database("10.16.89.71", "DWH"), 
  //Parameter
  _date_start = Date.ToText(
    #date(Date.Year(DateStart), Date.Month(DateStart), Date.Day(DateStart)), 
    "yyyy-MM-dd", 
    null
  ), 
  _date_end = Date.ToText(
    #date(Date.Year(DateEnd), Date.Month(DateEnd), Date.Day(DateEnd)), 
    "yyyy-MM-dd", 
    null
  ), 
  _pol_status = PolStat_Param, 
  //Native SQL
  _function = Value.NativeQuery(
    Source, 
    "SELECT * FROM [dbo].[Fn_TAProductionReport](@_date_start,@_date_end) 
        WHERE [PolicyStatus] = @_pol_status", 
    [date_start = @_date_start, date_end = @_date_end, pol_status = @_pol_status], 
    [EnableFolding = true]
  ), 
  output = _function
in
  output

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[HelloTheSun]

Hi @Anonymous 
It doesn't work