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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/267/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/267/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I filter Name to get just the ‘Grade_9’ row:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/267/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
This gives me just one table:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/267/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I click on the green ‘Table’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/267/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/267/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
This gives me the value, and I now rename my query DP_Grade_9:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/267/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
Since this query returns a value, the icon next to it indicates a whole number:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/267/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
Note that when I ‘Close & Load’ my queries to Excel, I should make sure that DP_Grade_9 is set to ‘Connection Only’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/267/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/267/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/267/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I create a query which is set to a single text value:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/267/image12.png/a1537847463e660a31158c8032525438.jpg)
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,
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/267/image13.png/917da985be13220165c8d2823e95344f.jpg)
I can ‘Convert to Parameter’ and it looks just like the other ‘P_Grade…’ parameters that I created:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/267/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2022/power-query/267/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
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!