So I have actually done this before with queries that I have built but this time I am trying to use a date pulled from an Excel table to filter a Power Query someone else built and isn't the cleanest code. I am having issues getting the code right and don't want to mess it up either. Below is the code I have for the Start_Date and End_Date.
let
Source = Excel.CurrentWorkbook(){[Name="Start_Date"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
Start_Date = Record.Field(#"Changed Type"{0},"Column1")
in
Start_Date
let
Source = Excel.CurrentWorkbook(){[Name="End_Date"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
End_Date = Record.Field(#"Changed Type"{0},"Column1")
in
End_Date
This gives me dates formatted as 12/29/2019 for the Start_Date and 6/26/2021 for the End_Date. I am then trying to use these in the below code to replace the dates that are currently in there.
let
Start_Date = Start_Date,
End_Date = End_Date,
Source = Sql.Database("", "", [Query="-- Estimated Runtime: <30 sec for 1 year of data#(lf)#
(lf)Declare @StartDate date#(lf)Declare @EndDate date#(lf)Set @EndDate = End_Date -- Set
EndDate to Now#(lf)Set @StartDate = '2019-12-29'--dateadd(yy,-1,dateadd(yy,
datediff(yy,0,@EndDate),0)) -- Set StartDate to January 1st of previous year#(lf)#(lf)
But I get the below error message if I just try to replace the dates with the named Start_Date and End_Date.
DataSource.Error: Microsoft SQL: Invalid column name 'End_Date'.
Details:
DataSourceKind=SQL
DataSourcePath=jansql01;mas500_app
Message=Invalid column name 'End_Date'.
Number=207
Class=16
Any help is greatly appreciated!
It needs to be in a text format that SQL can digest.
So instead of
Query="...Set @EndDate = End_Date..."
you need to insert End_Date as a string more like
Query="...Set @EndDate = '" & Date.ToText(End_Date, "yyyy-MM-dd") & "'..."