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 erro...
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
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