Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
faruux6
Regular Visitor

Convert Number to Date

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

2 ACCEPTED SOLUTIONS
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

Untitled.png

 

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

 

date.png

Hope this can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

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

 

View solution in original post

8 REPLIES 8
piyush_J
New Member

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
New Member

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
Not applicable

Thank you

bjh497
Frequent Visitor

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

 

like 2017 (integer) to 1905 (yyyy)

Anonymous
Not applicable

@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
Not applicable

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

 

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
Resident Rockstar
Resident Rockstar

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.

Untitled.png

 

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

 

date.png

Hope this can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.