cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OzzieFrog Regular Visitor
Regular Visitor

Extract and convert date from text string

Hello and happy new year to anyone reading this Smiley Happy 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 willreplicate 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
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Extract and convert date from text string

Hi @OzzieFrog,

 

Here I made a test by entering 04/01/19 directly. We can change the data type to using local in power query to work on it. Please check the steps as the pictures as below.

 

1.PNG2.PNG

 

Then we can get the result as we need.

 

3.PNG

 

 

Here is the M code for your refernce.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDRNzDUN7RUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"date", type text}}, "en-AU")
in
    #"Changed Type with Locale"

For more details, please check the pbix as attached.

 

Regards,

Frank

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
OzzieFrog Regular Visitor
Regular Visitor

Re: Extract and convert date from text string

FYI I have tried to edit this post three times to include return to lines and it keeps going back to this format. My apologies, there is obvisouly something wrong with either my computer or the pbi forum right now Smiley Happy
Community Support Team
Community Support Team

Re: Extract and convert date from text string

Hi @OzzieFrog,

 

Here I made a test by entering 04/01/19 directly. We can change the data type to using local in power query to work on it. Please check the steps as the pictures as below.

 

1.PNG2.PNG

 

Then we can get the result as we need.

 

3.PNG

 

 

Here is the M code for your refernce.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDRNzDUN7RUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"date", type text}}, "en-AU")
in
    #"Changed Type with Locale"

For more details, please check the pbix as attached.

 

Regards,

Frank

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Community Support Team
Community Support Team

Re: Extract and convert date from text string

Hi @OzzieFrog,

 

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.


Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.