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

[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 parameters for two dates (only this will change) this parameters will be on separate worksheet and by changing the dates query should update automatically.

reportdate and usage_period are the 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

 

[jeremy_norbury]

Depending on the source = SOMETHING( x, literal-sql ) , you'd need to convert the literal-sql to use parameters.

You could choose to either just provide parameters for the red highlighted bits OR take the whole SQL as a parameter from a table in another sheet, for example.

 

[krodriguez]

I just need to provide parameters for the red highlighted dates. what do you mean by the source? and convert?

 

[jeremy_norbury]

When you edit the query in the editor, the first line of the query (named source) contains the SQL.

You need to create 2 named parameters pStart and pEnd, for example and use those to replace the dates in red.

So you'll have something like :


SELECT commodity, typerc, Usage_period,

...snip...

WHERE 1=1
AND Commodity = 'e'
AND reportdate = '" & pStart & "' the rest


Go into the advanced editor , copy the whole thing and post it here if you're struggling and I'll show you the exact changes...

 

[krodriguez]

Here you go.

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

 

[jeremy_norbury]

You need to create 2 parameters : pReportDate (type any) and pUsagePeriod (type text)

You open the advanced editor and replace the Source = line with this.

let
Source = Sql.Database("usge-Supply", "usgeSupplyElec", [Query="SELECT commodity, typerc, Usage_period, ProductType2 #(lf), SUM([MWhs/dth]) [sumOf_MWhs/dth]#(lf), SUM(sumLineLoss) sumOf_sumLineLoss#(lf), SUM(sumResidualUsageLoad) sumOf_sumResidualUsageLoad#(lf), SUM(sumRemainingUsageLoad) sumOf_sumRemainingUsageLoad#(lf), SUM(sumStorageCapacityPlan) sumOf_sumStorageCapacityPlan#(lf), SUM(sumRemainingUsageLoad)+ SUM(sumStorageCapacityPlan) sumOf_NetLoad#(lf)FROM usage.vw_TLFSummary#(lf)WHERE 1=1#(lf)AND Commodity = 'e'#(lf)AND reportdate = '" & pReportDate & "' -- END OF QUARTER#(lf)AND calc_method = 'Simple'#(lf)and Usage_period > '"& pUsagePeriod &"' -- END OF QUARTER MONTH#(lf)GROUP BY commodity, typerc, Usage_period, ProductType2#(lf)#(lf)UNION ALL#(lf)#(lf)SELECT commodity, typerc, Usage_period, ProductType2 #(lf), SUM([MWhs/dth]) [sumOf_MWhs/dth]#(lf), SUM(sumLineLoss) sumOf_sumLineLoss#(lf), SUM(sumResidualUsageLoad) sumOf_sumResidualUsageLoad#(lf), SUM(sumRemainingUsageLoad) sumOf_sumRemainingUsageLoad#(lf), SUM(sumStorageCapacityPlan) sumOf_sumStorageCapacityPlan#(lf), SUM(sumRemainingUsageLoad)+ SUM(sumStorageCapacityPlan) sumOf_NetLoad#(lf)FROM usage.vw_TLFSummary#(lf)WHERE 1=1#(lf)AND Commodity = 'g'#(lf)AND reportdate = '"& pReportDate & "' -- END OF QUARTER#(lf)AND calc_method = 'Simple (Est Delivery Req)'#(lf)and Usage_period > '" & pUsagePeriod &"' -- END OF QUARTER MONTH#(lf)GROUP BY commodity, typerc, Usage_period, ProductType2 #(lf)ORDER BY commodity, typerc, Usage_period, ProductType2#(lf)"])
in
Source

You edit new values into the parameters to get a different usage period.

You could later convert this to pick up these 2 parameter values from a second table (e.g. above the table where the results of this query are presented...)

 

[krodriguez]

How I create the 2 parameters? what are the steps?

 

[jeremy_norbury]

How I create the 2 parameters? what are the steps?

Inside the Power query editor Home -> Parameters ->Manage -> New Parameter

Give them names and set their data type as stated previously.

 

[krodriguez]

I created the parameters but when I copy/paste the query in the source line, I'm getting this error "Expression.Error: The name 'pReportDate' wasn't recognized. Make sure it's spelled correctly"

 

[jeremy_norbury]

Post a screenshot of your parameters.

You apparently misspelled it - it's case sensitive.