Forum Discussion
Solved
Power Query - extracting Month Year from 1/1/2022
I have a date column formatted as 1/1/2022. I want to convert it in PQ to Jan 2022 but keep it as a date.
I've found a bunch of examples for DAX but they don't work. I found a Datetotext method but that turns it to text and the sorting doesn't work.
Is there an easy method to do this?
Hi Bryan123
it is as you described already. If you try to convert your date into something like "Jan 2022", the result will always be text. The reason is easy: Because there is no day anymore in this field, how could it be a valid date? So it's not just a formatting issue, but a data type issue.
If you really need it that way, you could keep the original date column, create a new column with the desired one ("Jan 2022") and sort that table based on the original date.
Or, if you load the query results back into an Excel table, only keep the original date and do the formatting directly in Excel.
Hi Bryan123
it is as you described already. If you try to convert your date into something like "Jan 2022", the result will always be text. The reason is easy: Because there is no day anymore in this field, how could it be a valid date? So it's not just a formatting issue, but a data type issue.
If you really need it that way, you could keep the original date column, create a new column with the desired one ("Jan 2022") and sort that table based on the original date.
Or, if you load the query results back into an Excel table, only keep the original date and do the formatting directly in Excel.
- That makes perfect sense. The reason I want this is so I don't have to manually click through multiple days for multiple months when filtering (I don't like the way it looks when grouping by years and months. What I do in Excel, and what I should have done here, is I create a date where all records in the same month has a date of the 1st. So, I just duplicated my date column, transformed it to only show the 1st of the month and as you said, did my formatting in Excel (mmm-yy)
