cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cze-Jhin
Frequent Visitor

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

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.
5 REPLIES 5
daxer-almighty
Solution Sage
Solution Sage

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.

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

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Greg_Deckler
Super User
Super User

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

 

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.