Power Query: Riveting Results Part 10
16 February 2022
Welcome to our Power Query blog. This week, I show how the FilePath Parameter allows me to change the workbook I extract data from.
My salespeople will be back soon. This week, I continue looking at the exam results I created in Power Query: Riveting Results Part 1:
I will be grading the results, and I will be using this example to explore parameters. Last week, I created ‘EDP_Grade_’ parameters, which were extracted from another workbook.
I can go back to the Exam Results query and use these parameters.
I showed in Power Query: Riveting Results Part 7 that it is not possible to edit using the cog next to the ‘Assigned Grade’ step to change the parameters, as they are not shown in the dialog unless they are true Power Query parameters. I looked at the difference between Power Query parameters and other queries that can be used as parameters in Power Query: Riveting Results Part 5.
I use the Advanced Editor, available on the Home tab, to change the M code:
I change the ‘Assigned Grade’ step from this:
#"Assigned Grade" =
Table.AddColumn(#"Changed Type", "Grade", each
if [Result] > DP_Grade_9 then 9
else if [Result] > DP_Grade_8 then 8
else if [Result] > DP_Grade_7 then 7
else if [Result] > DP_Grade_6 then 6
else if [Result] > DP_Grade_5 then 5
else if [Result] > DP_Grade_4 then 4
else if [Result] > DP_Grade_3 then 3
else "Ungraded")
to this:
#"Assigned Grade" =
Table.AddColumn(#"Changed Type", "Grade", each
if [Result] > EDP_Grade_9 then 9
else if [Result] > EDP_Grade_8 then 8
else if [Result] > EDP_Grade_7 then 7
else if [Result] > EDP_Grade_6 then 6
else if [Result] > EDP_Grade_5 then 5
else if [Result] > EDP_Grade_4 then 4
else if [Result] > EDP_Grade_3 then 3
else "Ungraded")
This has no immediate effect on the results of the query:
To show how FilePath allows me to point to another workbook, I have another workbook where I have the same Named Cells for grading bands set up. You should note that I must close the Power Query Editor in the workbook containing Exam Results before I can edit another workbook, so I ‘Close & Load To’ and choose ‘Connection Only’ for the new ‘EDP_Grade_’ queries and Base Query:
The new workbook I have created has different values for the grading bands:
The workbook also has a different name and is located in a different folder.
I close the new workbook, and go back to the workbook containing the Exam Results query, where I select the FilePath parameter in the Queries pane, and use the ‘Manage Parameter’ button to access the dialog:
I change the ‘Current Value’ to the new workbook name and location:
When I go back to the Exam Results query, the results have changed:
The results have changed, and it’s not looking good for the class!
Note that if users of the workbook containing Exam Results needed to maintain the FilePath parameter without editing in Power Query, I could link FilePath to a Named Excel Cell in the workbook as I have done for the ‘DP_Grade_’ and ‘EDP_Grade_’ parameters.
Come back next time for more ways to use Power Query!
