Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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. Detail...
Need Help
  • zzcc's avatar
    zzcc
    5 years ago

    Hi, Anonymous 

     

    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!

     

zzcc's avatar
zzcc
Frequent Visitor
5 years ago

Hi, Anonymous 

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),
    #"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"

 

 

 

Zerrick

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    zzcc
    Frequent Visitor
    5 years ago

    Hi, Anonymous 

     

    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!