Change whole number to a date

[Rosepowerbi]

I have a period 202201 which is formatted as a whole number.

I want to add a custom column which is in a date format and looks like Jan 22

Please help!

Thanks

Rose

[AmiraBedh · Accepted solution]

Hi again, last time I didn't pay attention to your input, I tried to reproduce it using Power Query like below :

= Table.AddColumn(#"Renamed Columns", "Custom", each
if Text.End([Period], 2) = "01" then "Jan" & " " & Text.Middle([Period], 2, 2)
else if Text.End([Period], 2)= "02" then "Feb" & " " & Text.Middle([Period], 2, 2)
else if Text.End([Period], 2) = "03" then "Mar" & " " & Text.Middle([Period], 2, 2)
else if Text.End([Period], 2) = "04" then "Apr" & " " & Text.Middle([Period], 2, 2)

else ....
else "")

[Ibukun_Oyeleke]

For example: your date column came in this numeric format....20141002......YYYYMMDD

You can convert it to the date type without generating errors by following these steps.

1. Highlight the column

2. Right click on the highlighted column and select "Change Type."

3. Select "Text"

4. Right click on the highlighted column again and select "Change Type."

6. Select "Date"

7. There'll be a pop up showing "Change Column Type", Select "Add new step.

8. Then you have the date type without errors.

(2014/10/02)

I hope this helps 🙏

[Rosepowerbi]

Hi thanks but I'm still getting an error, my period is a whole number format, not text

I need 'Jan 22' to be a date format

thanks

Rose

[Rosepowerbi]

thanks for your quick response

I got an error when I tried the first bit

[AmiraBedh]

What is the error message that you get ?

I already tried it using DAX : 

and in Power Query : 

[Rosepowerbi]

I tried to add a custom column in power query with your formula NewColumn= Date.FromText(Text.From([Period], "0000-00"))

It just says error in all the rows of the table, I didn't get a specific error message

[AmiraBedh]

In Power Query : NewColumn= Date.FromText(Text.From([Period], "0000-00"))

In DAX : MonthYear = FORMAT(DATE(LEFT('Table'[Period],4),RIGHT('Table'[Period],2),1),"mmm yy")