Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I wonder if you can help me with an issue I have with Power BI. My data source is a stored procedure which has several parameters with default values, however I would like the users to have the ability to change the parameter values when refreshing the data.
This is the current script in the query editor, which uses the default settings:
let
Source = Sql.Database("ServerName", "DatabaseName", [Query="EXEC [dbo].[brptARAge] #(lf)#(tab) @Company = 1, #(lf)#(tab) @Month = '7/30/2019' ,#(lf) @AgeDate = '7/30/2019',#(lf) @BegCust = 0,#(lf) @EndCust = 99999999,#(lf) @RecType = NULL,#(lf) @IncludeInvoicesThrough = '7/30/2019',#(lf) @IncludeAdjPayThrough = '7/30/2019',#(lf) @AgeOnDueorInv = 'D',#(lf) @LevelofDetail = 'I',#(lf) @DeductDisc = 'Y',#(lf) @DaysBetweenCols = 30,#(lf) @AgeOpenCredits = 'N',#(lf) @BegCustName = ' ',#(lf) @EndCustName = 'zzzzzzzzzzzzzzz',#(lf) @Sort = 'S';", CommandTimeout=#duration(0, 0, 10, 0)])
in
Source
I have created all the parameters but I am not sure how to modify the script so that when the data is refreshed the user is prompted to enter new parameter values if required, if not then they will use the default values.
Solved! Go to Solution.
Simply replace the text with your parameter name. For example, if you have a parameter called Parameter1 that has the name of the Company, then you could do this:
let
Source = Sql.Database("ServerName", "DatabaseName", [Query="EXEC [dbo].[brptARAge] #(lf)#(tab) @Company = Parameter1, #(lf)#(tab) @Month = '7/30/2019' ,#(lf) @AgeDate = '7/30/2019',#(lf) @BegCust = 0,#(lf) @EndCust = 99999999,#(lf) @RecType = NULL,#(lf) @IncludeInvoicesThrough = '7/30/2019',#(lf) @IncludeAdjPayThrough = '7/30/2019',#(lf) @AgeOnDueorInv = 'D',#(lf) @LevelofDetail = 'I',#(lf) @DeductDisc = 'Y',#(lf) @DaysBetweenCols = 30,#(lf) @AgeOpenCredits = 'N',#(lf) @BegCustName = ' ',#(lf) @EndCustName = 'zzzzzzzzzzzzzzz',#(lf) @Sort = 'S';", CommandTimeout=#duration(0, 0, 10, 0)])
in
Source
Simply replace the text with your parameter name. For example, if you have a parameter called Parameter1 that has the name of the Company, then you could do this:
let
Source = Sql.Database("ServerName", "DatabaseName", [Query="EXEC [dbo].[brptARAge] #(lf)#(tab) @Company = Parameter1, #(lf)#(tab) @Month = '7/30/2019' ,#(lf) @AgeDate = '7/30/2019',#(lf) @BegCust = 0,#(lf) @EndCust = 99999999,#(lf) @RecType = NULL,#(lf) @IncludeInvoicesThrough = '7/30/2019',#(lf) @IncludeAdjPayThrough = '7/30/2019',#(lf) @AgeOnDueorInv = 'D',#(lf) @LevelofDetail = 'I',#(lf) @DeductDisc = 'Y',#(lf) @DaysBetweenCols = 30,#(lf) @AgeOpenCredits = 'N',#(lf) @BegCustName = ' ',#(lf) @EndCustName = 'zzzzzzzzzzzzzzz',#(lf) @Sort = 'S';", CommandTimeout=#duration(0, 0, 10, 0)])
in
Source
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
66 | |
60 |