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!
