Power Query: a Function Function
6 September 2017
Welcome to our Power Query blog. Today I look at an easy way to view and test M code functions against data in my workbook.
My blog comes of age this week as it hits the big 4-0! I am feeling quite empowered, so that gives me an idea! In M-Powered, I described how to get to a description of the functions that can be used in M code to manipulate data by accessing the Microsoft help pages. There is however another way to get to the functions. Let me show you.
I begin with a blank query:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-40-image1.png/fca79b8e8f952fb0e55232b57fbe9921.jpg)
In the formula bar I enter the formula
=#shared
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-40-image2.png/63ab48b61c5e216835054d583e9ac972.jpg)
The screen displays a list of elements in my workbook, and a list of functions available to me. In order to investigate further, I choose the option at the top left of the screen in the ‘Convert’ section, namely ‘Into Table’ (feel free to select something else if you can find it!):
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-40-image3.png/11ece3f079afaf28b45053410d1f0229.jpg)
The top rows show the queries in my workbook, and if I click on ‘Table’ next to one of them I can see the data in my query:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-40-image4.png/88aa344cba0909a559e4695b45d62fb1.jpg)
Going back to my list of functions, further down the rows I can see the List.Range function that I used in One Route to a Running Total. I try clicking on the ‘Function’ label next to it:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-40-image5.png/d8ef72190dd906dc7740c8f8456d106e.jpg)
In the background, I can see a full description of the List.Range function, and I also have the option of trying out the function on one of my columns. Clicking the ‘Choose Column’ button accesses another screen:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-40-image6.png/d43855f754a9cda3a10edb75cea5c794.jpg)
I can choose any query in my workbook to try out the function.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-40-image7.png/f179ad12550edbe913d6475aeedcdf24.jpg)
Once I pick a query, I can see all the columns in that query and the first few rows of data. I choose List as the column to use.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-40-image8.png/51176996d7d91dcfa87910779e309c12.jpg)
I am taken back to the previous pop-up screen so that I can enter the offset and count if I wish. I choose an offset of 1 and a count of 3 and continue.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-40-image9.png/9bd639d8199e8512b1d713232a7161f7.jpg)
I have tested the List.Range function and it has come back with the first three entries in my list column.
I can also call the #shared function from within a query to test out a function (or look for one). I choose to add a step by clicking the fx button in the Ribbon above my Query1, as shown below:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-40-image10.png/b00e40119be0f8875c9af292498be3c5.jpg)
I choose to add my ‘#shared’ step:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-40-image11.png/50618c2b5a4009b7c4294f069175f967.jpg)
I can see the same record as before and I can go on to try out a function against any data in my workbook. This time let’s try something different:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-40-image12.png/211a56d9b15b50f0a86303238eb39a77.jpg)
Having acquired my answer, I can then continue with my query
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-40-image13.png/1a62a2ea0feb1a0330675bacf3315667.jpg)
If I delete the extra steps I have created, from ‘Custom1’ to ‘InvokedFunctionValue’ I can get back to my original query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-40-image14.png/169c287a84c0f24bd45a66f633a06bf3.jpg)
Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!