Forum Discussion
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 suppose I could manually standardize/update it, as it is not a large file, but I would like to get to know it in code.
All help appreciated - please keep in mind I'm a beginner.
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
15 Replies
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 Custom1Hello, 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
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
