Parameters

[busybee]

I have a Power BI file and have imported an Excel file using Edit Queries. I created 2 new parameters. When I try to create a function to dynamically change the parameter value on the Excel sheet I imported, Power BI says the Sheet does not reference any parameters. Any suggestions on how to resolve this?

[edhans]

Power BI/Power Query cannot change data on an external source, which is what it sounds like you are trying to do. If that is not the case, can you be more specific and perhaps provide a PBIX file as an example?

[busybee]

I took the following steps.

1) Create a new .pbix file. 

2) Open Query Editor using Edit Queries

3) Choose New Source as an Excel file and then import it.

4) A query named Sheet1 is created.

4) Click on Manage Parameter-New Parameter and create 2 new parameters.

5) Now when I right click on Sheet1 and click Create Function..., Power BI says the query Sheet1 does not reference any parameters. 

How do I resolve this? I want to create a function that utilizes the 2 parameters I created when invoked? 

[edhans · Accepted solution]

You are missing a step between 4 and 5.

Creating parameters doesn't put them in a query. You have to reference the parameters in the query. So to test this, assume your parameters are numbers, and one is called parTestValue. Create a custom column that is =[SomeNumericColumn] * parTestValue

Now you can right-click on the query and create a function. It will only be using the parameter(s) you are explicitly using in the query.

[busybee]

Thanks. That solution worked.