Forum Discussion

Brass Contributor

Sep 29, 2022

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...

excel

Martin_Weiss
Sep 30, 2022
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.

Martin_Weiss

Bronze Contributor

Sep 30, 2022

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.

Bryan123

Brass Contributor

Sep 30, 2022

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)