Forum Discussion

lornafnb's avatar
lornafnb
Helper I
4 years ago
Solved

Multiple date formats in column

Good day - my first post on the Forum! :)-   I'm reading from an Excel file, and the format of the date column is either 2021/11/08, or March '22 (in this case, it can default to 2022/11/01). I su...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Leave everything, in a custom column put this. That's it. 

    = Date.From(if Text.Contains(Text.From([Live date]),"'") then "1"&Text.Replace([Live date],"'","") else [Live date])

     If you want to convert this column to a date, select column - Transform tab - Detect data type

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

Since you are beginner, I will suggest that you insert a new custom column and put following formula where [Date] needs to be replaced with your column

= Date.From(if Text.Contains([Date],"'") then "1"&Text.Replace([Date],"'","") else [Date])

If you want to do in place column replacement, then insert following statement where Source needs to be replaced with your previous step.

= Table.ReplaceValue(Source,each [Date],each Date.From(if Text.Contains([Date],"'") then "1"&Text.Replace([Date],"'","") else [Date]),Replacer.ReplaceValue,{"Date"})

 See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ3NNQ3sFCK1YlW8k0sSs5QiCk1MDAyNzJSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    Custom1 = Table.ReplaceValue(Source,each [Date],each Date.From(if Text.Contains([Date],"'") then "1"&Text.Replace([Date],"'","") else [Date]),Replacer.ReplaceValue,{"Date"})
in
    Custom1

 

lornafnb's avatar
lornafnb
Helper I
4 years ago

Hello, thank you very much for your response.

1. I have tried the first option, but it gives me an error - would you mind if I send you the file here? I'm not sure how to attach it here.

2. How do I create a blank query (3rd point in your reply)?

So much appreciated. Thanks

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    1. Make sure [Date] is replaced with your column name.

    2. Ideal will be to upload the file without confidential/sensitive data to a cloud storage service such as Onedrive/Google Drive/Dropbox/Box (Onedrive preferred) and share the link here.

    3. For Blank query - Right click on left side pane - New Query - Blank Query

    • lornafnb's avatar
      lornafnb
      Helper I
      4 years ago

      Unfortunately my organization prevents using any of these services

    • lornafnb's avatar
      lornafnb
      Helper I
      4 years ago

      1. My Field name is 'Live date' so my new column looks like this..

      Date.From(if Text.Contains([Live date],"'") then "1"&Text.Replace([Live date],"'","") else[Live date])

      2. The [Live date] is currently a text field.

      3. The New field is also a text field - the "replace" part in the script above works...(although it is still Text format..

      4. However the dates that were (correct) dates before, produce the error.

      I suspect it might be a Data type error?

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        Can you click on one of the errors and post the error here? To have the column in date format, use below step (Replace Source with your previous step)

        = Table.AddColumn(Source, "Custom", each Date.From(if Text.Contains([Live date],"'") then "1"&Text.Replace([Live date],"'","") else[Live date]), type date)

         OR you can select the Custom column - Transform menu - Detect data type