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, 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"
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous ,

       

      I think the issue is that for BigQuery the Date in this WHERE statment needs to be in double quotation marks. I've tried this in BigQuery without these and it doesn't work. See below from BigQuery:

      But when I add "" at the start and end, to date format yyyy-MM-dd, it works:

      So how can I use this set up and add "" around the Date field?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Anonymous - I think the following should help add the " around the Date string

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkmtKFGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Add Text", each "Text"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Add Test with Quotes", each """Test"""),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each """" & [Column1] & """")
in
    #"Added Custom2"