Dynamic M query with snowflake

[swrlabhe]

Hi All

i am trying to setup a dynamic M query parameter with snowflake and date field.

i have created the parameter in report and trying pinning in the query but somehow it always shows syntax error.

the parameter name is AsOfDate

appreciate your help in this 

[v-kpoloju-msft]

Hi @swrlabhe,

Thank you for reaching out to the Microsoft fabric community forum. Also thanks to @danextian, @lbendlin, for those valuable insights on this thread. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

From snowflake I take server name from account shown in the image and warehouse name is COMPUTE_WH.

outcome:

I perfectly executed the Dynamic M query with the sample data, you can verify the below .pbix file.

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @swrlabhe,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @swrlabhe,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[v-kpoloju-msft]

Hi @swrlabhe,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[danextian · Accepted solution]

Hi @
Try something like below:

let
  AsOfDateParam = AsOfDate,
  FormattedDate = Date.ToText(AsOfDateParam, "yyyy-MM-dd"),
  Source = Sql.Database(
    "YourServerName",
    "YourDatabaseName",
    [
      Query = "
            SELECT OrderID, CustomerID, OrderDate, TotalAmount
            FROM Sales
            WHERE OrderDate >= '"
        & FormattedDate
        & "'"
    ]
  )
in
  Source

[srlabhe]

Sorry replying from slightly different login , 

I ahve below M query code which says 0 errors detected 

let

selectedDate= 

Text.Combine({"declare query_parameters (",

       "startTime:datetime=date(",Date.ToText(AsOfDate,"MM/DD/YYYY"),"),"

            //"repoNames: dynamic=dynamic ([",selectedReporNames,"]));"

            }) ,

    Source = Value.NativeQuery(Snowflake.Databases("devvps.azwh.east-us-2.azure.privatelink.snowflakecomputing.com","APP_CORE_WH_M",

    [Role="TAM_16874_DEV_READ"]){[Name="TMLC_DB"]}[Data], 

    "with desk_mandates as (#(lf)select desk_mandate_id, max(dm_updated_ts) as dm_updated_ts 

    from TMLC_DB.TM_REPORTING.V_DESK_MANDATE where dm_updated_ts <= '"& selectedDate &"'"& " group by desk_mandate_id#(lf)) #(lf)

    select vdm.*,dateadd(day,1,cast(date_trunc('day',(vdm.dm_updated_ts)) as date)::TIMESTAMP_NTZ) 

    dm_updated_ts_daymidnight#(lf)from TMLC_DB.TM_REPORTING.V_DESK_MANDATE vdm#(lf)

    join desk_mandates dm on vdm.desk_mandate_id=dm.desk_mandate_id 

    and vdm.dm_updated_ts = dm.dm_updated_ts#(lf)where vdm.dm_updated_ts <= '"& selectedDate &"'", null, [EnableFolding=true])

in

   Source

it gives me below error when try to run the M query

DataSource.Error: ODBC: ERROR [22007] Timestamp 'declare query_parameters (startTime:datetime=date(01/DD/YYYY),' is not recognized
Details:
DataSourceKind=Snowflake
DataSourcePath=devvps.azwh.east-us-2.azure.privatelink.snowflakecomputing.com;APP_CORE_WH_M
OdbcErrors=[Table]

Help appreciated

[swrlabhe]

Direct query 

[lbendlin]

Is your SnowFlake connection in Direct Query or in Import Mode?

[swrlabhe]

I have the connection in Import

[lbendlin]

Dynamic M Query parameters only work for Direct Query data sources.