Power Query: Riveting Results Part 5

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:

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!