Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Extract and convert date from text

Hello and happy new year to anyone reading this :) I am hoping someone will have a clever idea for the following issue: I am extracting various data fields from Oracle and have a specific issue with a text field containing multiple dates. Below is an example of the "Comments" field content: "CP OS PD 19/08/19 ROS 19/08/19 FU 04/01/19 - FAT to be witnessed by Portland staff 02/01/19 handover from Karen" Below are the three columns I would like to create along with their content, in date format: . Promised Date (ref PD above): 19/08/19 as in 19 Aug 2019 (Australian format) . Required On Site (ref ROS above): 19/08/19 as in 19 Aug 2019 . Follow Up (ref FU): 04/01/19 as in 04 Jan 2019 While PowerBI deals with all my Oracle date fields correctly, and while I am successful in extracting the date from the comment column, the moment I change the data type to date format it flips the year and days around so 04/01/19 (04 Jan 2019) becomes 19 Jan 2004. Below is a formula I used to extract one of the dates (I would replicate the methodology for the three columns) and where hopefully you have a suggestion to enable a conversion in the appropriate Australian format rather than International: Promised Date (calculated) = if(search("PD",Query1[Comments],1,0)>0, MID(Query1[Comments],search("PD",Query1[Comments],1,0)+3,8), "01/01/00") I have tried to split columns based on delimiters but unfortunately not all lines will have the same information so the split returns incorrect values. Thanks in advance for any suggestion. OF
Need Help

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous

    Create calculated columns

    Promised Date = VAR testdate =
    IF (
        SEARCH ( "PD", Sheet1[comment], 1, 0 ) > 0,
        MID ( Sheet1[comment], SEARCH ( "PD", Sheet1[comment], 1, 0 ) + 3, 8 )
    )
VAR day =
    LEFT ( testdate, 2 )
VAR month =
    MID ( testdate, 4, 2 )
VAR year =
    RIGHT ( testdate, 2 )
RETURN
    DATE ( 2000 + year, month, day )


Required Date =
VAR testdate =
    IF (
        SEARCH ( "ROS", Sheet1[comment], 1, 0 ) > 0,
        MID ( Sheet1[comment], SEARCH ( "ROS", Sheet1[comment], 1, 0 ) + 4, 8 )
    )
VAR day =
    LEFT ( testdate, 2 )
VAR month =
    MID ( testdate, 4, 2 )
VAR year =
    RIGHT ( testdate, 2 )
RETURN
    DATE ( 2000 + year, month, day )

Follow Up  Date = 
VAR testdate =
    IF (
        SEARCH ( "FU", Sheet1[comment], 1, 0 ) > 0,
        MID ( Sheet1[comment], SEARCH ( "FU", Sheet1[comment], 1, 0 ) + 3, 8 )
    )
VAR day =
    LEFT ( testdate, 2 )
VAR month =
    MID ( testdate, 4, 2 )
VAR year =
    RIGHT ( testdate, 2 )
RETURN
    DATE ( 2000 + year, month, day )

     

    Best Regards

    Maggie