How can I add date parameters to run a query in power query?

[krodriguez]

Hello,

I have a query using power query in excel (see below), and looking to speed up the process of updating the results using date parameters for two dates (only this will change) these dates will be on separate worksheet and by changing the dates query should update automatically and eliminate the extra steps on opening the and edit the query.

reportdate and usage_period are the date fields I want to use parameters to run the report, therefore, I can run it without going in the workbook queries and edit the query and change it manually.

Someone can give me a hand on how to do this, will appreciate it. Thanks!




SELECT commodity, typerc, Usage_period, ProductType2
, SUM([MWhs/dth]) [sumOf_MWhs/dth]
, SUM(sumLineLoss) sumOf_sumLineLoss
, SUM(sumResidualUsageLoad) sumOf_sumResidualUsageLoad
, SUM(sumRemainingUsageLoad) sumOf_sumRemainingUsageLoad
, SUM(sumStorageCapacityPlan) sumOf_sumStorageCapacityPlan
, SUM(sumRemainingUsageLoad)+ SUM(sumStorageCapacityPlan) sumOf_NetLoad
FROM usage.vw_TLFSummary
WHERE 1=1
AND Commodity = 'e'
AND reportdate = '3/31/2017' -- END OF QUARTER
AND calc_method = 'Simple'
and Usage_period > '201703' -- END OF QUARTER MONTH
GROUP BY commodity, typerc, Usage_period, ProductType2


UNION ALL


SELECT commodity, typerc, Usage_period, ProductType2
, SUM([MWhs/dth]) [sumOf_MWhs/dth]
, SUM(sumLineLoss) sumOf_sumLineLoss
, SUM(sumResidualUsageLoad) sumOf_sumResidualUsageLoad
, SUM(sumRemainingUsageLoad) sumOf_sumRemainingUsageLoad
, SUM(sumStorageCapacityPlan) sumOf_sumStorageCapacityPlan
, SUM(sumRemainingUsageLoad)+ SUM(sumStorageCapacityPlan) sumOf_NetLoad
FROM usage.vw_TLFSummary
WHERE 1=1
AND Commodity = 'g'
AND reportdate = '3/31/2017' -- END OF QUARTER
AND calc_method = 'Simple (Est Delivery Req)'
and Usage_period > '201703' -- END OF QUARTER MONTH
GROUP BY commodity, typerc, Usage_period, ProductType2
ORDER BY commodity, typerc, Usage_period, ProductType2