Power Query: Example Grading
4 September 2019
Welcome to our Power Query blog. Today, I use Column From Examples to help me put my data into grade boundaries.
I have some data for components used in my fictional tent hire business:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/144/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I want to check what category each component falls into. There are several ways of doing this, but today, I want to see how Column From Examples may help me.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/144/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I have created a query for my data using ‘From Table’ on the ‘Get & Transform’ section of the ‘Data’ tab. On the ‘Add Column’ tab, I have the option to create a ‘Column from Examples’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/144/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
Since I am only interested in grading the deviation, I want to create my column using data from Deviation only.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/144/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I fill in at least one from each category, and then Power Query provides the M code to calculate the grade.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/144/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I can then edit the M code to use the correct boundaries:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/144/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
The M code I have used is:
= Table.AddColumn(#"Renamed Columns", "Custom", each if [Deviation] >= 0.8 then "Too Big" else if [Deviation] >= 0.5 then "Caution" else if [Deviation] >= -0.5 then "Pass" else if [Deviation] >= -0.8 then "Caution" else "Too Small", type text)
This has saved me the job of working out the logic! I may now save this change which will apply the correct boundaries to all of my data.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/144/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
Come back next time for more ways to use Power Query!