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

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"
Anonymous's avatar
Anonymous
Not applicable
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