cancel
Showing results for
Did you mean:
Helper I

convert to date format

Hi,

I am getting below error in date column. could you please help to convert to date format (mm/dd/yyyy)

"power bi DataFormat.Error: We couldn't parse the input provided as a Date value. Details: 44201"

suren

1 ACCEPTED SOLUTION
Frequent Visitor

Hi, @suren947

column:

``````Column =
IFERROR(FORMAT(('Table'[Column1]-DATE(1899,12,30))+DATE(1899,12,30),"mm/dd/yyyy"),BLANK())``````

measure:

``````Measure =
IFERROR(FORMAT((MAX('Table'[Column1])DATE(1899,12,30))+DATE(1899,12,30),"mm/dd/yyyy"),BLANK())``````

result:

Zerrick

Did I answer your question? Mark my post as a solution!

11 REPLIES 11
Frequent Visitor

Hi, @suren947

To create a measure like this:

``Measure = (MAX('Table'[Column1])-DATE(1899,12,30))+DATE(1899,12,30)``

or create a column:

``col = ([Column1]-DATE(1899,12,30))+DATE(1899,12,30)``

result:

Zerrick

Did I answer your question? Mark my post as a solution!

Super User IV

You can add a custom column (or adapt a transform column step) with this formula

= try Date.From([Date]) otherwise Date.AddDays(#date(1900,1,1), Number.FromText([Date]))

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

Helper I

Thank you Pat. Your expression works well but when date column has some text values(N/A..) it shows error

Suren

Frequent Visitor

Hi, @mahoneypat

You are absolutely right, obviously I am still not familiar with the PoweQuery language. It definitely saves a lot of steps.
But what confuses me is that in Excel it seems that the first day is January 1, 1900, and the result I get after converting the numbers to dates in Power BI indicates that the first day should be December 30, 1899, which is a little strange.
But your formula is definitely correct anyway. Thanks for sharing.

@suren947  if you want to get result in Power Query, here is the improved M code:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY3MtU3MjAyVIrViVYyMTEyNgOzgOJGqOKWYBaYUFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
a = Table.AddColumn(#"Changed Type", "1899-12-30", each try Date.From([Column1]) otherwise Date.AddDays(#date(1899,12,30), Number.FromText([Column1])))//,
//b = Table.AddColumn(a, "1900-1-1", each try Date.From([Column1]) otherwise Date.AddDays(#date(1900,1,1), Number.FromText([Column1])))
in
a//b``````

Zerrick

Did I answer your question? Mark my post as a solution!

Super User IV

Thanks for that correction.  I didn't check the math on the date change.  I checked it with today's date (44409 as integer in Excel), and had to update the formula to this instead.

= try Date.From([Date]) otherwise Date.AddDays(#date(1899,12,31), Number.FromText([Date])-1)

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

Helper I

Thank you zerick.

But requirement is to convert this serial format(44236, 44239) to MM/DD/YYYY in query editor

Suren

Frequent Visitor

Hi, @suren947

measure

``````Column =
var _1=([Column1]-DATE(1899,12,30))+DATE(1899,12,30)
return FORMAT(_1,"mm/dd/yyyy")``````

column

``````Measure =
var _1=(MAX('Table'[Column1])-DATE(1899,12,30))+DATE(1899,12,30)
return FORMAT(_1,"mm/dd/yyyy")``````

result:

In power query

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY3MtU3MjAyVIrViVYyMTEyNgOzgOJGqOKWSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try (Duration.From([Column1]))
otherwise null),
otherwise null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type duration}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type1", "Custom.2", each #date(1899,12,30)+[Custom]),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom2", {{"Custom.1", type text}, {"Custom.2", type text}}, "en-US"),{"Custom.1", "Custom.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Removed Columns1" = Table.RemoveColumns(#"Merged Columns",{"Custom", "Column1"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Merged", type date}})
in
#"Changed Type2"``````

Zerrick

Did I answer your question? Mark my post as a solution!

Helper I

Thank you Zerrick.

Below expression works for me but encounter errors "Cannot convert value '' of type Text to type Number." when column has "" or "N/A". Could you please help me to fix

Column2 =
var _1=([Column1]-DATE(1899,12,30))+DATE(1899,12,30)
return FORMAT(_1,"mm/dd/yyyy")
Frequent Visitor

Hi, @suren947

column:

``````Column =
IFERROR(FORMAT(('Table'[Column1]-DATE(1899,12,30))+DATE(1899,12,30),"mm/dd/yyyy"),BLANK())``````

measure:

``````Measure =
IFERROR(FORMAT((MAX('Table'[Column1])DATE(1899,12,30))+DATE(1899,12,30),"mm/dd/yyyy"),BLANK())``````

result:

Zerrick

Did I answer your question? Mark my post as a solution!

Super User IV

@suren947 , do you have all values as number , that can days from 1-1-1970 or 1-1-1900

Date =date(1900,1,1) + [Days]

or

Date =date(1970,1,1) + [Days]

or

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helper I

Hi Amit,

My date field contains below values.Could you please suggest to format as mm/dd/yyyy

Thanks

Announcements

Manage your user group events

Check out the News & Announcements to learn more.

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Top Kudoed Authors