Power Query: Riveting Results Part 5
12 January 2022
Welcome to our Power Query blog. This week, I continue creating parameters from Excel cells.
My salespeople are missing in action. 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 a Named cell and examined the contents of the Excel Workbook in Power Query:
I filter Name to get just the ‘Grade_9’ row:
This gives me just one table:
I click on the green ‘Table’.
I now have the value of Grade_9 in the column. I can remove the ‘Changed Type’ step and right-click and drill down on the value.
This gives me the value, and I now rename my query DP_Grade_9:
Since this query returns a value, the icon next to it indicates a whole number:
Note that when I ‘Close & Load’ my queries to Excel, I should make sure that DP_Grade_9 is set to ‘Connection Only’:
This is a default for queries created as Parameters in Power Query, but not for DP_Grade_9, as it has been created from a query.
Which brings me to another point. I right-click on DP_Grade_9 in the Queries pane. There is an option to ‘Convert to Parameter’ but it is greyed out.
Whilst the final result of my query is a single value, I am not allowed to convert it. This option is only available if I create a very simple query which equals a value. I can create a new Blank Query to demonstrate this by right-clicking in the Queries pane:
I create a query which is set to a single text value:
The M code I used to create this is simply:
= “This can be converted to a parameter”
When I right click on this query in the Queries pane,
I can ‘Convert to Parameter’ and it looks just like the other ‘P_Grade…’ parameters that I created:
I would like this to be available for queries like DP_Grade_9 too, so that I could have the current value in brackets and the ability to select it as a parameter from other functions. However, this is not an option. I suspect this is because the query is converted to Metadata, as indicated by the Advanced Editor view of I am a parameter:
The previous source step is no longer available. This would imply that I can’t keep the previous steps of DP_Grade_9 and convert it to a parameter. I’ve seen this question on forums, and this is my conclusion!
Next time, I will replace my Power Query maintained parameters with Excel maintained ‘DP_’ versions in the Exam Results query.
Come back next time for more ways to use Power Query!