Hi,
I'm using PowerQuery to access and return data in an SQL table. The entire unfiltered table is enormous so I would rather the SQL query return the selected data rather than to return it all and then use PQ to filter it.
I have saved the start and end date into the functions : fnGetStartDate and fnGetEnddate using 2 versions of the below code
The query I need to return contains the half hourly energy use for a site and I only want to return a weeks worth...
I am using a fnGetSiteName to filter after the data has been requested - but how can I replace the date values with fnGetStartDate and fnGetEndDate ?
Thanks in advance
I'm using PowerQuery to access and return data in an SQL table. The entire unfiltered table is enormous so I would rather the SQL query return the selected data rather than to return it all and then use PQ to filter it.
I have saved the start and end date into the functions : fnGetStartDate and fnGetEnddate using 2 versions of the below code
Rich (BB code):
let
Source = Excel.CurrentWorkbook(){[Name="tblParameters"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Parameter", type text}, {"Value", type date}}),
Value = #"Changed Type"{0}[Value]
in
ValueThe query I need to return contains the half hourly energy use for a site and I only want to return a weeks worth...
Rich (BB code):
let
Source = Odbc.Query("dsn=SystemsLink",
"SELECT Lookup.Lookup_Name, Contacts.Name, DataProfile.Date, #(lf)Sum(DataProfile.[00:30]) AS [00:30], Sum(DataProfile.[01:00]) AS [01:00], Sum(DataProfile.[01:30]) AS [01:30], Sum(DataProfile.[02:00]) AS [02:00], Sum(DataProfile.[02:30]) AS [02:30], Sum(DataProfile.[03:00]) AS [03:00], Sum(DataProfile.[03:30]) AS [03:30], Sum(DataProfile.[04:00]) AS [04:00], Sum(DataProfile.[04:30]) AS [04:30], Sum(DataProfile.[05:00]) AS [05:00], Sum(DataProfile.[05:30]) AS [05:30], Sum(DataProfile.[06:00]) AS [06:00], Sum(DataProfile.[06:30]) AS [06:30], Sum(DataProfile.[07:00]) AS [07:00], Sum(DataProfile.[07:30]) AS [07:30], Sum(DataProfile.[08:00]) AS [08:00], Sum(DataProfile.[08:30]) AS [08:30], Sum(DataProfile.[09:00]) AS [09:00], Sum(DataProfile.[09:30]) AS [09:30], Sum(DataProfile.[10:00]) AS [10:00], Sum(DataProfile.[10:30]) AS [10:30], Sum(DataProfile.[11:00]) AS [11:00], Sum(DataProfile.[11:30]) AS [11:30], Sum(DataProfile.[12:00]) AS [12:00], Sum(DataProfile.[12:30]) AS [12:30], Sum(DataProfile.[13:00]) AS [13:00], Sum(DataProfile.[13:30]) AS [13:30], Sum(DataProfile.[14:00]) AS [14:00], Sum(DataProfile.[14:30]) AS [14:30], Sum(DataProfile.[15:00]) AS [15:00], Sum(DataProfile.[15:30]) AS [15:30], Sum(DataProfile.[16:00]) AS [16:00], Sum(DataProfile.[16:30]) AS [16:30], Sum(DataProfile.[17:00]) AS [17:00], Sum(DataProfile.[17:30]) AS [17:30], Sum(DataProfile.[18:00]) AS [18:00], Sum(DataProfile.[18:30]) AS [18:30], Sum(DataProfile.[19:00]) AS [19:00], Sum(DataProfile.[19:30]) AS [19:30], Sum(DataProfile.[20:00]) AS [20:00], Sum(DataProfile.[20:30]) AS [20:30], Sum(DataProfile.[21:00]) AS [21:00], Sum(DataProfile.[21:30]) AS [21:30], Sum(DataProfile.[22:00]) AS [22:00], Sum(DataProfile.[22:30]) AS [22:30], Sum(DataProfile.[23:00]) AS [23:00], Sum(DataProfile.[23:30]) AS [23:30], Sum(DataProfile.[24:00]) AS [24:00]
FROM Lookup INNER JOIN ((Contacts INNER JOIN Points ON Contacts.Id = Points.Contacts_Id) INNER JOIN DataProfile ON Points.Id = DataProfile.Point_Id) ON Lookup.Lookup_Id = Contacts.Group_1
GROUP BY Lookup.Lookup_Name, Contacts.Name, DataProfile.Date, DataProfile.Date
HAVING (((DataProfile.Date) Between '6/4/2018' And '6/10/2018'))
ORDER BY DataProfile.Date;"),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns1", each ([Site] = fnGetSiteName))
in
#"Filtered Rows"I am using a fnGetSiteName to filter after the data has been requested - but how can I replace the date values with fnGetStartDate and fnGetEndDate ?
Thanks in advance
