cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cosmicyes
Frequent Visitor

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 😞

1 ACCEPTED SOLUTION
moizsherwani
Helper V
Helper V

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 )

 

moizsherwani_0-1631970492635.png

 

 

View solution in original post

5 REPLIES 5
CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2021-09-18 160512.png

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

moizsherwani
Helper V
Helper V

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 )

 

moizsherwani_0-1631970492635.png

 

 

View solution in original post

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

aj1973
Community Champion
Community Champion

Hi @cosmicyes 

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

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!