To create dynamic input parameter in Power Query using SQL Server

 1. Create a database and a table to fetch data from into Excel 2013 with some values inserted.

 

2. Do check that Power Query plugin is installed.

 3. Import data from Sql server into Excel 2013 using Power Query option given above.

 4. Enter the necessary credentials and the SQL query to import data.

 5. After clicking OK above window will be displayed. Before doing dynamic filtering, we should first use define static filter. Therefore select arrow on Emp_address & perform filter as per your criteria.

 6. Here it shows filter where Emp_address equals London. Then click OK.

 7. Now when you click on view tab and then Advanced Editor you will find the following code.

 8. Create a new simple excel sheet with two parameters[Parameter Name & Value] as shown and save the sheet.

 9. Give the new excel sheet a name [Pass_Parameter] and make first row as header.

 10. Open up the sheet and select any one value to perform a drill down operation.

 11. Now view the code in Advanced Editor and copy those two lines selected.

 12. You will see the sheet like this after performing drill down operation.

 13.Now open the main sheet and paste the code copied from previous Editor as shown. But change the Source [here 'Par'] and Value [here CityValue (you can give of your choice)] and hit OK.

 14. Now write the parameter value to filter [Here I gave as 'Mumbai'].

15. When you click refresh on left side as shown in circle, you will get the filtered result. You can give different parameter value as per your choice to filter the dataset. That's all!!!
 
        Thank you !!!