Forum Discussion

swrlabhe's avatar
swrlabhe
Helper I
1 year ago
Solved

Dynamic M query with snowflake

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 

Need Help
  • danextian's avatar
    danextian
    1 year ago

    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

10 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

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

      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

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

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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's avatar
      srlabhe
      Super User
      1 year ago

      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
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community Support
    1 year ago

    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's avatar
      v-kpoloju-msft
      Community Support
      1 year ago

      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's avatar
        v-kpoloju-msft
        Community Support
        1 year ago

        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.