Please note javascript is required for full website functionality.

Blog

Power Query: More Haste Less Steps Part 3

4 May 2022

I have a Dates query, which I have started to improve. This was the original 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.  Last time, I looked at how I could have achieved the same result with fewer steps, whilst still using the User Interface (UI).  I concentrated on the creation of the Month Name column, which I achieved in just one step by using the Extract dropdown on the Transform tab:

This saved me four [4] steps compared to the original query!  This time I’ll look at how I can improve the functionality used to create a Quarter column in my required format.

In my original query, I took five [5] steps to create a ‘Quarter’ column.  I duplicated the Date column again (Duplicated Column1) and used the Date transformation again, this time to get the Quarter, creating a ‘Calculated Quarter’ step.

I then decided I wanted to have a ‘Q’ in front of it, so I converted the column to data type text using the dropdown from the data type icon, creating a ‘Changed Type2’ step:

I then added a ‘Custom Column’ from the Home tab to add the ‘Q’ (‘Added Custom’):

Then I deleted the Date – Copy column which held the original quarter value (‘Removed Column1’).

On my new improved query, instead of duplicating the Date column and converting it into a Quarter column, I simply add a new column from the ‘Add Column’ tab, using the Date dropdown:

This creates a new column called Quarter:

Instead of converting this to data type Text and creating a custom column, I can use the Format dropdown on the Transform tab:

This allows me to ‘Add Prefix’, which will work without me changing the data type first.

I then have my column in the format I want, in just two [2] steps!

This means that my 12 steps have been reduced to four [4]:

I’ll leave you with a question for next time: how can I change the order of the columns Month Name and Quarter, using the User Interface (UI), without adding an extra step?

 

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

Newsletter