Please note javascript is required for full website functionality.


Power Query: More Haste Less Steps Part 2

27 April 2022

Welcome to our Power Query blog.  This week I look again at a query with too many steps.


Last time, I looked at a Dates query:

I deliberately went the long way round to create this query!  Ignoring the first three [3] steps, it has taken me 12 steps to achieve my goal.  This time I will look at how I could have achieved the same result with less steps, whilst still using the User Interface (UI).

The first task was to create a Month Name column with a short month name format.  Last time, this took six [6] steps.  One of the reasons for this is that I used the Transform tab options, which meant I had to duplicate columns to keep my original.  Many functions exist on the Transform tab and the ‘Add Column’ tab for precisely this reason.  Instead of duplicating a column like I did last time,

I can go to the ‘Add Column’ tab, and add a column from the ‘Dates’ section:  

I can then add the Month Name column in one step.  

This replaces the two [2] steps I took to achieve this last time.  The next step is to change Month Name to show the first three [3] characters of the month.  Last time, I used ‘Split Column’ on the Transform tab to split the column ‘By Positions’, creating a ‘Split Column by Position’ step:  

This automatically generated a ‘Changed Type1’ step.  I then had to delete the unwanted column, creating a ‘Removed Column’ step and rename the one I wanted to keep to ‘month’, creating a ‘Renamed Column1’ step:  

Would it surprise you to learn that I could create a column containing the short name in one step?  This time, the Transform tab is the most appropriate:  

Instead of splitting the column, I can simply transform this column using ‘Extract First Characters’:  

This gives me the new format for Month Name in one step:  

Next time, I’ll look at how I can improve the functionality used to create a Quarter column in my required format.


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