Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Working on a Power Query that calls a stored procedure from SQL Server. I can hard code a year, 2021 in this example, but I would like to parameterize the year.
let
Source = Sql.Database("1.1.1.1", "SQLDatabase", [Query="exec [dbo].[OUT_StoredProc] 2021"])
in
Source
The goal is to have the user change the year in an Excel workbook cell. I have gotten a query to pull the data from the cell and update when the year is changed:
let
Source = Excel.CurrentWorkbook(){[Name="Year"]}[Content],
YearVar = Source{0}[Column1]
in
YearVar
My question is the syntax for passing the “YearVar” to the stored procedure? I.e., replacing the hard coded “2021” with “YearVar”? All the things I have tried return an error.
You will need to use a pattern like this, breaking the text string of your sql into parts. FYI you could just use a parameter in your PBI report instead of separately storing the year value in an Excel workbook.
sqlquery = "first part of sql ... year = " & yearquery & " ... rest of sql"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.