Power Query dynamic pass parameter to a stored procedures

[MaxMattiello79]

Hello,

I build an Excel file that load some data calling a stored procedures using Power Query and i need to dynamically pass the parameter to that stored procedure.

The Excel file looks like the attached image (see image below) and it contains the following element:

- a query called StudyList that using a SQL statement read a list of project from a SQL database

- a query called PROMAC that execute a stored procedure that has an input parameter that should be one of the project that user select from the StudyList query

The page contains a Pivot Table where the Study Code field from StudyList query is included in the filters section of the Pivot and some fields from PROMAC query are present in teh ROW and VALUES section.

What i would like to implement is to pass to the stored procedure (under PROMAC query) the study selected from the list.

Is that possible? If so how?

I see that i can define a table and use that table as parameter for the stored procedure but the issue if that as i'm using a Pivot i'm not able to define a table name because it is not supported.

Thanks

Massimo

[v-chuncz-msft · Accepted solution]

@MaxMattiello79 ,

You may take a good look at the following post.

https://community.powerbi.com/t5/Desktop/parameterize-connection/m-p/205900#M90712