Convert Number to Date

[faruux6]

hey

So I have a following issue: I want to convert a column which includes numbers like "1995", "2000" and "2005" to date (YYYY). How can I do that. 

thanks for your help

[v-piga-msft · Accepted solution]

Hi @faruux6,

By my tests, you could change the data tyoe in Query Editor.

1. Go to Query Editor and change the column type from number to text, then change the text type to date. 

You should note that when you have a remind, you should select the add a new step. Then you will get the data type.

2. Close && Apply. Go to Power BI Data view and click the column and change the data format of the column to 2001(YYYY).

Hope this can help you!

Best Regards,

Cherry

[piyush_J]

Very trivial but very useful. i almost got stuck and then realize power Bi can only indentify date in text but no date in numeric. great learning !!

[SeunOdeyemi]

Text To Date

=Date.FromText(Text.Range([Column1],0,4)&Text.Range([Column1],4,2)&Text.Range([Column1],6,2))

Number to Date

=Date.FromText(Text.Range(Number.ToText([Column1]),0,4) & (Text.Range(Number.ToText([Column1]),4,2)& (Text.Range(Number.ToText([Column1]),6,2))))

[Anonymous]

Thank you

[bjh497]

If you do a direct conversion to date, it can get a weird result.

like 2017 (integer) to 1905 (yyyy)

[Anonymous]

@bjh497  thats because a date value is actually an integer that stands for "Days since 31/12/1899".  By converting 2017 directly into a date, you have made the assumption that it would understand that 2017 is the year and not "days since"

[Anonymous · Accepted solution]

My recommendation is to do it explictally as part of the Edit Query.  If you have a column that contains only a Year Value, create a new column (Add Column -> Custom Column) called "YearStartDate".  The formula in the UI box should be:

= Date.FromText(Number.ToText([Year]) & "-01-01")

This will tell Power BI that you want a date value of that given year, as 1st January.  From here you can format the data as type Date.  Then use the formatting suggestion of @v-piga-msft

[omelo]

Hi , I do have a yearmonth number column like 202202. How could I get a startdate column like 01/02/2022?
Thank you

[v-piga-msft · Accepted solution]

Hi @faruux6,

By my tests, you could change the data tyoe in Query Editor.

1. Go to Query Editor and change the column type from number to text, then change the text type to date. 

You should note that when you have a remind, you should select the add a new step. Then you will get the data type.

2. Close && Apply. Go to Power BI Data view and click the column and change the data format of the column to 2001(YYYY).

Hope this can help you!

Best Regards,

Cherry