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.
excelente, me ayudo de mucho, ningun tutorial me ayudaba, hasta que vi este tutorial, es lo maximo !!!!
Thank you this was very helpful
Hi,
we typically only use YYYYMM so glad when I find your example where I get YYYY-MM.
But this is then changed into a text format. YYYYMM doesn’t seem to be able to be used as Date.
Not being in date format then I cannot use it fully for other things, right?
Or is there a way to have YYYYMM as being recognized as Date?
Thanks
You need a way to uniquely ID a month with a meaningful name. Some people just use the first day of the month (date format), some use the last day of the month (date format), or you can use a text or integer representation (surrogate date). It’s not right or wrong, just different. If you don’t use a date format, you won’t be able to use the auto visual tricks that compress dates into quarters, years, etc. You may be able to get YYYYMM changed to a date, but it would be a parsing event that converts the surrogate into a real date.
When I tried this, while single digit months came out as 2020-05, 2020-08 etc, it also gave me 2020-010 abd 2020-012. Unfortunately your screen shot above has code hidden so I couldn’t apply the code generated for your example….
When you are providing the examples, if this happens, you need to go to the row that is incorrect and add the correct result
This is awesome. Thank you for the post.
Not sure when Microsoft added this but this morning I happed to see that I can now display my date as 2001-03 (yyyy-mm).
Thanks Matt, really helped me out of a bind.
went a lot smoother than i thought!
Another great article Matt, thanks!
Thanks for sharing Matt!
Will have a play with this later as i know I have a solution where this could be used instead of deriving Year & Month from a date and then using a concatenation with a ‘-‘.
I’m also wondering if using this supports Query Folding.
I can say almost certainly that this will not support query folding, so do everything else first, and do this step last.
Thanks for sharing Andrew. Actually, I am a believer in learning through the UI. Use the UI to do the work and then look at the formulas and learn and hack away all you can. I think your observation is great and taking the time to do this will make you a better Power Query user.
Looking at the code generated by the wizard, I had the idea to simplify the expression for the added column to Table.AddColumn(#”Renamed Columns2″, “YYYY-MM”, each Date.ToText([Date], “yyyy-MM”)).
It seems like the format string in the Date.ToText() function allows any arbitrary combination of day month and year.
Of course, that does mean knowing the functions and writing M code in the “Custom Column” step instead of letting the “Column from Examples” work it out for you, but then again, I just feel funny when I see the Date.ToText(0 function used twice and then the results combined.
Nice tip.
It’s 6 of one and half a dozen of the other. As a rule, I prefer to do it in Power Query to keep all the transformations together. The exception is if I need to use a measure in the column, then I would prefer DAX. But it is personal preference.
Out of interest: what’s the benefit of doing this in Power Query compared with adding a calculated column and using FORMAT([Date],”yyyy-mm”)? TIA
What a time saver that Query By Example is! I usually do this same exact formatting with an if statement based on the month number and then a series of concatenations [Year] && “-‘ && [month] && ……. .
This has many more uses – thanks for sharing !
Ooh, nice! That had never occurred to me, there’s all kinds of date format changes that could be done much more easily this way, thanks for showing this!