Forum Discussion
Help Needed: Passing Dynamic Parameters to SQL Stored Procedure in Power BI
Hi manoj_0911 ,
Please refers to the following steps.
1.Open the Power Query editor and click Manage Parameters -> New to create a new parameter.2. Create parameters in the Manage Parameters dialog box.
3.Then connect to the SQL Server database.
In this SQL statement, the procedure name is followed by a series of values that correspond to the procedure parameters.
EXEC <YourProcedureName> 'Agent_Details','Group1,Group2','2024-08-01','2024-09-01'
4.After importing the data, go to the Power Query Editor again and open the Advanced Editor for the query you just imported, and copy the M code I provided earlier.Best Regards,
Dengliang LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi manoj_0911 ,
Please refer to the following M code.
let
Source = Sql.Database("<Server_Name>", "<Database_Name>",
[Query="EXEC <Your_ProcedureName> 'Agent_Details',
'"&SelectedGroups&"',
'" &Date.ToText(StartDate) &"',
'" &Date.ToText(EndDate) & "'"
]
)
in
Source
You can also refer to the following link for more information.
Execute SQL Server Stored Procedure With User Parameter In Power BI
Best Regards,
Dengliang Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for your reply , How and Where to create the parameters (SelectedGroups, StartDate and EndDate parameters) correctly, please help, also if you could please share the pbix file
Hi manoj_0911 ,
Please refers to the following steps.
1.Open the Power Query editor and click Manage Parameters -> New to create a new parameter.2. Create parameters in the Manage Parameters dialog box.
3.Then connect to the SQL Server database.
In this SQL statement, the procedure name is followed by a series of values that correspond to the procedure parameters.
EXEC <YourProcedureName> 'Agent_Details','Group1,Group2','2024-08-01','2024-09-01'
4.After importing the data, go to the Power Query Editor again and open the Advanced Editor for the query you just imported, and copy the M code I provided earlier.Best Regards,
Dengliang LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for you reply , let me try it first
I tried connecting powerbi to sql server stored procedure , i'm able to pass parameter values but, I'm not able to use them as Slicers/Filters in the Report.