Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power Query & SQL: Dynamic Date for Where Clause

Hi,   I'm having issues passing a dynamic date field through my SQL statement, in order to filter the data that comes into my report.   I'm using an IF statement within Power Query to determine w...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    SUCCESS!!!!

    I amended this slight to make have it as a standalone query, then I referenced it in my other queries, as below:

    QUERY: StartDateDynamic

     

    let
    Day = Number.ToText(#"Day (Number)"),
    Month = Number.ToText(#"Month (Number)"),
    Year = Number.ToText(Year),
    Out = Year&"-"&Month&"-"&Day,
    #"Converted to Table" = #table(1, {{Out}}),
    #"Added Custom2" = Table.AddColumn(#"Converted to Table", "Custom", each """" & [Column1] & """"),
    Custom = Text.Combine(#"Added Custom2"[Custom])
in
    Custom

    Then here is the reference in the BigQuery table:

     

    let
    Source = Value.NativeQuery(GoogleBigQuery.Database([UseStorageApi=true]){[Name=""&#"BigQuery Project Name"&""]}[Data], "SELECT#(lf)#(lf)m.PartnerID,#(lf)m.AdvertiserID,#(lf)m.CampaignID,#(lf)m.InsertionOrderID,#(lf)m.CreativeID,#(lf)m.CM360PlacementId,#(lf)m.LineItemID,#(lf)m.ExchangeID,#(lf)m.FloodLightActivityID,#(lf)UPPER(f.FloodLightActivityName) AS FloodLightActivityName,#(lf)m.Day_Dt AS Date,#(lf)m.TotalConversions,#(lf)m.PostViewConversions,#(lf)m.PostClickConversions,#(lf)m.CM360PostViewRevenue,#(lf)m.CM360PostClickRevenue#(lf) 
    FROM "&#"BigQuery Project Name"&"."&#"BigQuery Database"&".DV360_Metrics_F AS m
    JOIN "&#"BigQuery Project Name"&"."&#"BigQuery Database"&".DV360_Conversion_D AS f ON m.FloodLightActivityID = f.FloodLightActivityID#(lf)
    WHERE m.Day_Dt >= " & StartDateDynamic & "
    AND m.PartnerID = "&#"Partner ID"&"
    AND m.AdvertiserID "&#"Advertiser ID - In or Not In"&" ("&#"Advertiser ID"&")
    AND m.FloodLightActivityID <> -1", null, [EnableFolding=true]),
    #"Added Custom" = Table.AddColumn(Source, "Data Source", each "DISPLAY & VIDEO 360", Text.Type),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Date] >= RangeStart and [Date] <= RangeEnd)
in
    #"Filtered Rows"

    Thanks you SOOOO much for your help on this.

    Mark

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

 

I've tried this and I'm getting the following error:

 

Expression.Error: We cannot apply operator & to types Text and Date.
Details:
    Operator=&
    Left=SELECT *
    FROM res-apac-prd-skynet-au.BIReporting_DV360.DV360_Metrics_F
    WHERE Day_Dt >=  
    Right=01/04/2022

This makes me think it's something to do with the format of the HistoricalStartDate and CalendarStartDate fields. Would that be right? Any ideas on how I can resolve this?

 

Mark

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous, it looks like you need use Date.ToText( DynamicStartDate , "YYYY-MM-DD" ) function to convert the date value into the appropriate text string.  Sorry I don't know the correct syntax for the Big Query string.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi,

       

      I've added a step that changes DynamicStartDate as per your suggestion. When I did this I could see the field appear as a Date in the SQL window. But then I'm getting the following error:

       

      DataSource.Error: ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: No matching signature for operator >= for argument types: DATE, INT64. Supported signature: ANY >= ANY at [6:11]
Details:
    DataSourceKind=GoogleBigQuery
    DataSourcePath=GoogleBigQuery
    OdbcErrors=[Table]

       

      Is this because I'm asking the date to be greater than or equal to a Text field?

      Thanks,

      Mark

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thanks Anonymous  - it looks like Google Big Query is rejecting the query string because it is not formatted properly.  It would help in this scenario if you have another tool to test the query in.  This secondary tool will help you identify the error in the text string.

         

        Please consider the following example that I would normally use for SQL Server or Analysis Service queries that involve String.  The trick is to create the full query string before including in the Sql.Database or AnalysisService.Database query.  You can copy the full string from Power Query view into SSMS.

         

         

        let
    #"Some Date" = #date(2022, 8, 23),
    #"Some Product" = "Widgets",
    #"Convert Date" = Date.ToText( #"Some Date" , "yyyymmdd"),
    sql = 
"
SELECT A.* 
FROM schema.table A
WHERE date = '" & #"Convert Date" & "'
   AND
     product = '" & #"Some Product" & "';
"    ,
    #"Get Data" = Sql.Database( Server, Database , [Query = sql])
in
    #"Get Data"