Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Extract and convert date from text string

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...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

    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.

     

     

    Then we can get the result as we need.

     

     

     

    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

     

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi Anonymous,

 

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.

 

 

Then we can get the result as we need.

 

 

 

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

 

 

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi Anonymous,

 

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


Regards,
Frank