Turn a String into a Date

[cosmicyes]

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 😞

[CNENFRNL]

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"

[cosmicyes]

is this M? Unfortunately I am not familiar with M yet. But thank you!

[moizsherwani · Accepted solution]

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 )

[cosmicyes]

This looks MUCH easier than my solution. Thank you, that´s great!

[aj1973]

Hi @cosmicyes 

Did you try to use Power Query? And even with DAX why do you have to do it so often?