cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
suren947
Helper I
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"

 

Thanks in advance 

suren

1 ACCEPTED SOLUTION
zzcc
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:

zzcc_0-1627782165708.png

 

 

 

Zerrick

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

 

View solution in original post

11 REPLIES 11
zzcc
Frequent Visitor

Hi, @suren947 

To create a measure like this:

 

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

 

zzcc_0-1627696093792.png

or create a column:

 

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

 

result:

zzcc_1-1627696241406.png

 

Zerrick

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

 

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

 

Suren

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

zzcc_0-1627783717972.png

 

@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!

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thank you zerick.

 

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

 

Suren

zzcc
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:

zzcc_0-1627709439616.png

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),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each try(Date.From([Column1]))
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"

 

zzcc_0-1627711832036.png

 

 

Zerrick

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

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")
zzcc
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:

zzcc_0-1627782165708.png

 

 

 

Zerrick

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

 

View solution in original post

amitchandak
Super User IV
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!

Hi Amit,

 

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

 

suren947_0-1627669810728.png

 

Thanks

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

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

Top Solution Authors