Forum Discussion
Turn a String into a Date
I got some data from a very old ERP-System.
The date in the csv-file is this way:
* 80520 for May 8 2020
* 110520 for May 11 2020
So the string can have 5 or 6 characters.
I solved it myself using some string-operations in DAX but this is VERY complicated and I need to do this very often.
Is there an easier way?
I tried to add some lines of my code in a previos post but it was marked as spam and I don´t know why 😞
Not sure why the DAX formula you created is complicated. See below which works fine for your sample data. You can just create this as a calculuated column.
Date = VAR YearVal = "20" & RIGHT ( 'Table'[ERP System], 2 ) VAR MonthVal = LEFT ( RIGHT ( 'Table'[ERP System], 4 ), 2 ) VAR DateVal = LEFT ( 'Table'[ERP System], LEN ( 'Table'[ERP System] ) - 4 ) RETURN DATE ( YearVal, MonthVal, DateVal )
5 Replies
Not sure why the DAX formula you created is complicated. See below which works fine for your sample data. You can just create this as a calculuated column.
Date = VAR YearVal = "20" & RIGHT ( 'Table'[ERP System], 2 ) VAR MonthVal = LEFT ( RIGHT ( 'Table'[ERP System], 4 ), 2 ) VAR DateVal = LEFT ( 'Table'[ERP System], LEN ( 'Table'[ERP System] ) - 4 ) RETURN DATE ( YearVal, MonthVal, DateVal )This looks MUCH easier than my solution. Thank you, that´s great!
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjAwNTJQitWJVjI0hDBjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Old = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Old", Int64.Type}}), #"Converted Date" = Table.AddColumn(#"Changed Type", "Date_PQ", each Date.From(Number.ToText([Old], "0/00/00"), "en-GB")) in #"Converted Date"is this M? Unfortunately I am not familiar with M yet. But thank you!
