How to solve "Cannot convert value '25.06.21' of type Text to type Date"?

[Cze-Jhin]

Hi All,

I am trying to extract the date information from a text sample as such in PowerBI: "ABC Company 25.06.21"

So I have tried to use the following methods to extract the date: 

Version 1: Generated Date = DATEVALUE(SUBSTITUTE(RIGHT('Table1'[DataSetName],8, ".", "/"))

Version 2: Generated Date = FORMAT(RIGHT('Table1'[DataSetName],8), "DD/MM/YYYY")

Version 3: Generated Date= DATEVALUE(RIGHT('Table1'[DataSetName],8))

Version 4: Manually convert the column from text to date

But none of it work, PowerBI keep showing me the error "Cannot convert value '25.06.21' of type Text to type Date"

Can anyone help please? I would really appreciate it.

@PowerBI @PowerQueryFTW 

[daxer-almighty]

I'd like to point out that such things should always be done either in the data source or in Power Query. Never in DAX unless you have a very, very, VERY strong reason to deviate from Best Practices.

[Cze-Jhin]

Hi @daxer-almighty , thank you very much for the heads up, will take note on that.

[Greg_Deckler]

@daxer-almighty You Shouldn't Use DAX for That - Microsoft Power BI Community

[Greg_Deckler]

@Cze-Jhin Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVnDOzy1IzKtUMDLVMzDTMzJUio0FAA==", 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}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1.2", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2", "Column1.2.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", Int64.Type}, {"Column1.2.2", Int64.Type}, {"Column1.2.3", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each #date(2000+[Column1.2.3],[Column1.2.2],[Column1.2.1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1.2.1", "Column1.2.2", "Column1.2.3"})
in
    #"Removed Columns"

DAX

Column = 
    VAR __FirstPeriod = SEARCH(".",[Column1])
    VAR __Year = RIGHT([Column1],2) + 2000
    VAR __Month = MID([Column1],__FirstPeriod+1,2)
    VAR __Day = MID([Column1],__FirstPeriod-2,2)
RETURN
    DATE(__Year,__Month,__Day)

[Cze-Jhin]

Hi @Greg_Deckler , thank you very much for the help!