I was answering a question for a student in one of my online training courses this week. The question was, “Do you know of a way in power query to efficiently extract YYYY-MM from a Date column?” This can be done ‘manually’ with multiple steps including:
- Extract the Year into 1 column
- Extract the Month Number into a second column.
- Deal with the need for a leading zero before the months 1 through 9
- Merge the columns together with a “-” as a separator
If you know how to write M code, you could manually write a single line that will do the step for you. But I am a believer in using the UI to help you when ever possible – you should only have to resort to M code on rare occasions.
Let me show you how to do this using the Add Columns from Examples feature.
To get some sample data happening, I used a Calendar table that I had previously produced and stored in OneNote. You can read my article showing how to create a calendar table using Power Query if you want to get this code. The technique will work with any table that has a Date column.
These are the steps to follow:
- Edit the query that contains the Date column in Power Query.
- Select the Date column.
- Select Add Column from the menu tab.
- Select Query By Example\Query by Selection.
A dialog box appears prompting you to enter example values in the new column that you are adding.
- Give an example for the first month in the first row (see below).
- Next, give an example for the second month (see below). It is important to note that this is not the second row in the table, it is row number 32. The reason I chose this row is that doing so helps the wizard see the pattern in the data
The wizard then recognises the pattern and adds the rest of the values in the new column. Note: at this point I should have renamed the column from “Custom” to something like “YYYY-MM” but I forgot to do that. I changed it after closing the wizard.
- Check the values for correctness and click on OK.
- Now I renamed the column. Note in the video, I did this by editing the line of code generated by the wizard rather than renaming the column itself.
- Click Close & Apply.
The table is then loaded. You can now add the column YYYY-MM to a Matrix or Table and view the values.
I recorded the steps in the following video as it makes it easy to see how the end to end process works.
Want to Learn Power Query in a Structured Way?
My Power Query Online training course consists of 7.5 hours of online video training material that will teach you everything you need to know about using this fabulous tool for both Power BI and Excel. You can watch some free sample videos and find out more about registration following the link above.