Forum Discussion

cosmicyes's avatar
cosmicyes
Helper II
4 years ago
Solved

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 usi...
Need Help
  • moizsherwani's avatar
    moizsherwani
    4 years ago

    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 )

     

     

     

CNENFRNL's avatar
CNENFRNL
Community Champion
4 years ago
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's avatar
cosmicyes
Helper II
4 years ago

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