Please note javascript is required for full website functionality.
MVP

Blog

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:

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.

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’:

Since I am only interested in grading the deviation, I want to create my column using data from Deviation only.

I fill in at least one from each category, and then Power Query provides the M code to calculate the grade.

I can then edit the M code to use the correct boundaries:

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.

Come back next time for more ways to use Power Query!

Newsletter