Forum Discussion

manoj_0911's avatar
manoj_0911
Kudo Commander
1 year ago
Solved

Help Needed: Passing Dynamic Parameters to SQL Stored Procedure in Power BI

Help Needed: Passing Dynamic Parameters to SQL Stored Procedure in Power BI
 
Hi Community,
 
I am working on a Power BI project and need help with calling a **SQL stored procedure** dynamically in Power Query. My knowledge of stored procedures is very limited, so I'm struggling with setting this up.
 
Here's what I am trying to achieve:
1. I want to use a stored procedure (`p_Get_UStatus`) from my SQL Server database to fetch data.
2. The stored procedure requires the following parameters:
   - `@v_detail` – This will always be `'Agent_Details'`.
   - `@v_group_names` – A comma-separated list of group names (e.g., `'group1,group2'`).
   - `@v_start_time` – Start date.
   - `@v_end_time` – End date.
3. I need these parameters (`group names`, `start date`, `end date`) to be **dynamic** so that they can be controlled from Power BI.
 
**What I Have Tried So Far:**
1. I wrote the following query in Power Query's Advanced Editor:
   ```M
   let
       Source = Sql.Database("", "DEV", 
           [
               Query = Text.Format("EXEC dbo.p_Get_UStatus 
                                    @v_detail = 'Agent_Details', 
                                    @v_group_names = '#{0}', 
                                    @v_start_time = '#{1}', 
                                    @v_end_time = '#{2}'", 
                                   {SelectedGroups, StartDate, EndDate})
           ])
   in
       Source
   ```
2. I created parameters in Power BI for:
   - `SelectedGroups` (comma-separated list of groups).
   - `StartDate` (Date type).
   - `EndDate` (Date type).
 
3. When I run the query, I get errors like:
   - **Expression.SyntaxError: Token Literal expected.**
 
---
 
**Questions:**
1. Is this the right way to call a stored procedure with dynamic parameters in Power BI?
2. Do I need to change anything in the stored procedure itself?
3. How can I pass the `@v_group_names`, `@v_start_time`, and `@v_end_time` parameters dynamically?
4. If someone has experience with a similar setup, could you guide me step-by-step?
 
Any help or insights would be greatly appreciated! Thanks in advance.
 
----
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    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 Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

    • manoj_0911's avatar
      manoj_0911
      Kudo Commander
      1 year ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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 Li

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    I have the same issue.  I created a parameter with values tied to another table called SortBy which holds the valid list of parameters.

    I have a slicer on the canvas tied to Sort Value

    No action is taken when I select a value from that slicer.  

    I've been reading about binding a field to a parameter, but I do not have that option in my modeling - advanced settings.  

    Field parameters as a Preview Feature is checked in Options.

     

    How can I expose the parameter the user so they can dynamically select a value that will refresh the visual with the correct passed parameter?

     

     

     

    • Mahee's avatar
      Mahee
      Regular Visitor
      1 year ago

      HI manoj_0911 ,

      Could you pls confirm whether this approach works for azure sql mi as well?