Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

Reply
peterhinton
Frequent Visitor

Declare parameters for SQL query in Power BI

So i have the below code from SQL i use to populate some date,

 

DECLARE @CW INT, @CM INT, @CQ VARCHAR(4);
SET @CW=19; SET @CM = 5; SET @CQ = 'QTR2'

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 = @CW THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) AS 'Week'
, SUM(CASE WHEN SA.FISCALYEAR = 2016 AND SA.FISCALWEEK = @CW THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) AS 'LY Week'
 ,sum(CASE WHEN SA.FISCALYEAR = 2017 AND SA.FISCALWEEK = @CW THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) / SUM(CASE WHEN SA.FISCALYEAR = 2016 AND SA.FISCALWEEK = @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

Where i declare CW = 19, CM = 5, CQ = 'QTR2',

 

 

I need to be able to change these values each week, can i be propted to chnage these on work book open perhaps ?

 

Im very new to both PowerBI and kinda new to SQL,

 

I can do it in Excel by create a macro to replace the values with that of a cell vlaue which works fine, but my manager wants us to start using PowerBI more

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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.

1.PNG

Regards,

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

@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.

1.PNG

Regards,

Hi!

How would you declare for Date field?

I tried below query and getting some error

 

let SQLSource = (Week as date) =>

 

------

 

In main query have included below condition:

[PrdDate] BETWEEN DATEADD(WW,-5,Convert(Date,(Week))) AND DATEADD(W,1,convert(Date,(Week)))

 

getting below error on invoking the function,

DataSource.Error: Microsoft SQL: Invalid column name 'Week'.
Invalid column name 'Week'.
Details:

DataSourceKind=SQL
DataSourcePath=.;DBName
Message=Invalid column name 'Week'.
Invalid column name 'Week'.
Number=207
Class=16

 

Kindly help!

@Anonymous

 

You Sir,

 

Are a freeking GENIOUS,

 

Thank YOu so much, i literally spent my whole Friday Afternoon trying multiple ways of achieving this,

 

I think i was missing

    SQLSource = (CW as number) =>

 

And also this part

 

Number.ToText

So onw i will play round with these until i fully understand what they do But again Thank You it works for now 🙂

 

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors