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

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!

 

amitchandak
Super User
Super User

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

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