Declare parameters for SQL query in Power BI

peterhinton,

Based on your SQL statement, I note that you only use the @CW parameter in your SQL query.

In Power BI desktop, click “Get Data->Blank Query”, then click Advanced Editor, paste the following code in it(replace server name and database name with your owns). 

let
    SQLSource = (CW as number) =>

let
    Source = Sql.Database("servername", "databasename", [Query="SELECT
--CHOOSE BRANCH INFO TO USE AND FILTER IN PIVOT
SA.BRANCHNO As ' Branch No'
,EU.[STORE NAME] As 'Branch Name'
,EU.COUNTRY
,EU.TERRITORY
,EU.DISTRICT
,EU.REGION
,EU.DSM
,EU.RSM
,EU.[STORE TYPE]


--SUM WEEK SALES FOR CURRENT WEEK
, SUM(CASE WHEN SA.FISCALYEAR = 2017 AND SA.FISCALWEEK = "& Number.ToText(CW) & " THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) AS 'Week'
, SUM(CASE WHEN SA.FISCALYEAR = 2016 AND SA.FISCALWEEK = "& Number.ToText(CW) & " THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) AS 'LY Week'
 ,sum(CASE WHEN SA.FISCALYEAR = 2017 AND SA.FISCALWEEK = "& Number.ToText(CW) & " THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) / SUM(CASE WHEN SA.FISCALYEAR = 2016 AND SA.FISCALWEEK = "& Number.ToText(CW) & " THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) - 1 as 'WEEK VAR'


-- FROM EU ACTIVE STORES, MANUALLY UPLOADED EACH FRIDAY BY PETER HINTON
FROM
[BHXSQL2014-DEV].[BMANALYTICS].[DBO].[EUACTIVESTORES] EU

-- JOIN WITH SALES AGGREGATE FROM MARK TRIMMERS LINKED SERVER
INNER JOIN
EUUKSQL01.DASHBOARD.DBO.SALESAGGREGATEWEEK SA
ON SA.BRANCHNO = EU.[STORE NO]

--SELECT ONLY MAIN CHAIN STORES
WHERE
EU.[UPLOAD TYPE]='MAIN' AND EU.COMPSTATUS = 'COMP'

GROUP BY 
SA.BRANCHNO
,EU.[STORE NAME]
,EU.Country
,EU.Territory
,EU.District
,EU.Region
,EU.DSM
,EU.RSM
,EU.[Store Type]

ORDER BY SA.BRANCHNO"])

in
    Source
in
    SQLSource

Then you can enter parameter values and click “Invoke” button to generate filtered table as shown in the following similar screenshot.



Regards,