Long story short:
I'm using parameter dates (From & To) in m query per "mock" code below
I want to get rid of filter "
" and instead use "
"
It is crucial for to have parameters but the date load hangs because of the amount of rows. Is there anyway I can pass my parameters onto SQL to ensure I only get lines per my dynamic parameter?
BTW I have only started using SQL & M Query so if someone can manipulate the code and post in answer and I will then dissect it for my own learning that would be great
I'm using parameter dates (From & To) in m query per "mock" code below
Power Query:
let
Source = Sql.Database("azeusvmsql03", "GreatLake", [Query="SELECT dim.Calendar.FiscalYear, dim.Calendar.FiscalMonthNumber, dim.Calendar.FiscalQuarter, dim.LocationLatest_v.StoreNumber, dim.Calendar.FiscalYearWeek, SUM(fact.StockChange.NetQuantity) AS Shipments,
dim.Calendar.CalendarDate AS TransactionDate
FROM fact.StockChange INNER JOIN
dim.StockChangeDetail ON fact.StockChange.StockChangeDetailKey = dim.StockChangeDetail.StockChangeDetailKey INNER JOIN
dim.Calendar ON fact.StockChange.CalendarKey = dim.Calendar.CalendarKey INNER JOIN
dim.LocationLatest_v ON fact.StockChange.LocationLatestKey = dim.LocationLatest_v.LocationLatestKey
GROUP BY dim.StockChangeDetail.TransactionTypeCode, dim.Calendar.CalendarDate, dim.LocationLatest_v.StoreNumber, dim.Calendar.FiscalYear, dim.Calendar.FiscalMonthNumber, dim.Calendar.FiscalQuarter,
dim.Calendar.FiscalYearWeek
HAVING (dim.StockChangeDetail.TransactionTypeCode = 8) AND (dim.Calendar.CalendarDate = CONVERT(DATETIME, '2022-02-07 00:00:00', 102))"]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each [TransactionDate]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Custom", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Custom] >= InputVariableFrom) and ([Custom] <= InputVariableTo) or ([Custom] >= InputVariableFrom2) and ([Custom] <= InputVariableTo2)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
#"Removed Columns"
I want to get rid of filter "
Power Query:
(dim.Calendar.CalendarDate = CONVERT(DATETIME, '2022-02-07 00:00:00', 102))
Power Query:
([Custom] >= InputVariableFrom) and ([Custom] <= InputVariableTo)
It is crucial for to have parameters but the date load hangs because of the amount of rows. Is there anyway I can pass my parameters onto SQL to ensure I only get lines per my dynamic parameter?
BTW I have only started using SQL & M Query so if someone can manipulate the code and post in answer and I will then dissect it for my own learning that would be great