Power Query & SQL: Dynamic Date for Where Clause

[markhollandau]

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 which of 2 dynamic dates to use. I've got this set up in my Calendar table and it's working fine. But when I try to apply the same login to my SQL statement, if doesn't work.

Here are the date fields and IF statements I'm using:

• CalendarStartDate: this uses 3 number format parameters to create a date (#date(Year,#"Month (Number)",#"Day (Number)")
• HistoricalStartDate: this also uses number format parameters to create a date (Date.AddMonths(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1),-#"Months of Historical Data")
• DynamicStartDate: this is where my IF statement comes in, to decide which of the 2 dates above to use (if HistoricalDataStartDate >= CalendarStartDate then HistoricalDataStartDate else CalendarStartDate)

I've then tried to reference this step in my query, as below:

let
    CalendarStartDate = #date(Year,#"Month (Number)",#"Day (Number)"),
    HistoricalDataStartDate = Date.AddMonths(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1),-#"Months of Historical Data"),
    DynamicStartDate = if HistoricalDataStartDate >= CalendarStartDate then HistoricalDataStartDate else CalendarStartDate,
    Source = Value.NativeQuery(GoogleBigQuery.Database([UseStorageApi=true]){[Name=""&#"BigQuery Project Name"&""]}[Data], "SELECT *
    FROM "&#"BigQuery Project Name"&"."&#"BigQuery Database"&".DV360_Metrics_F
    WHERE Day_Dt >= DynamicStartDate
    AND PartnerID = "&#"Partner ID"&"
    AND AdvertiserID "&#"Advertiser ID - In or Not In"&" ("&#"Advertiser ID"&")
    AND FloodLightActivityID <> -1", null, [EnableFolding=true]),
    #"Added Custom" = Table.AddColumn(Source, "Data Source", each "DISPLAY & VIDEO 360", Text.Type)
in
    #"Added Custom"

When I do this, I get the following error:

DataSource.Error: ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: Unrecognized name: DynamicStartDate at [6:21]
Details:
    DataSourceKind=GoogleBigQuery
    DataSourcePath=GoogleBigQuery
    OdbcErrors=[Table]

I'm assuming my date format is incorrect?

I've also tried creating a query to reference in the same statement, as below - the query is Dynamic State Date

let
    Source = Value.NativeQuery(GoogleBigQuery.Database([UseStorageApi=true]){[Name=""&#"BigQuery Project Name"&""]}[Data], "SELECT *
    FROM "&#"BigQuery Project Name"&"."&#"BigQuery Database"&".DV360_Metrics_F
    WHERE Day_Dt >= "&#"Dynamic Start Date"&"
    AND PartnerID = "&#"Partner ID"&"
    AND AdvertiserID "&#"Advertiser ID - In or Not In"&" ("&#"Advertiser ID"&")
    AND FloodLightActivityID <> -1", null, [EnableFolding=true]),
    #"Added Custom" = Table.AddColumn(Source, "Data Source", each "DISPLAY & VIDEO 360", Text.Type)
in
    #"Added Custom"

When I try this, I get the following error, which also looks like it's referencing my format:

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/03/2022

I'm a bit stumped on this one. Any ideas on how I can achieve this?

Thanks,

Mark

[markhollandau]

Hi @Daryl-Lynch-Bzy ,

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

[Daryl-Lynch-Bzy]

Hi @markhollandau, 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.

[markhollandau]

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

[Daryl-Lynch-Bzy]

Thanks @markhollandau  - 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"

[markhollandau]

Hi @Daryl-Lynch-Bzy ,

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?

[Daryl-Lynch-Bzy]

@markhollandau - 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"

[markhollandau · Accepted solution]

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

[Daryl-Lynch-Bzy]

Hi @markhollandau - It looks like you are not escaping your text correctly.  Try this:

let
    CalendarStartDate = #date(Year,#"Month (Number)",#"Day (Number)"),
    HistoricalDataStartDate = Date.AddMonths(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1),-#"Months of Historical Data"),
    DynamicStartDate = if HistoricalDataStartDate >= CalendarStartDate then HistoricalDataStartDate else CalendarStartDate,
    Source = Value.NativeQuery(GoogleBigQuery.Database([UseStorageApi=true]){[Name=""&#"BigQuery Project Name"&""]}[Data], "SELECT *
    FROM " & #"BigQuery Project Name" & "." & #"BigQuery Database" & ".DV360_Metrics_F
    WHERE Day_Dt >=  " & DynamicStartDate & "
    AND PartnerID = " & #"Partner ID" & "
    AND AdvertiserID " & #"Advertiser ID - In or Not In" & " (" & #"Advertiser ID" & ")
    AND FloodLightActivityID <> -1", null, [EnableFolding=true]),
    #"Added Custom" = Table.AddColumn(Source, "Data Source", each "DISPLAY & VIDEO 360", Text.Type)
in
    #"Added Custom"