Forum Discussion
Two timezone in the same column from the same source
Hi guys,
I have some datas that are in YYYY-MM-DD, and some in DD/MM/YYYY, in the same column (the latest gives me an error that I can't transform in text, because the error comes at the first step). The funniest is that the dates that doesn't show an error are all wrong (it even gives me futur dates).
Anyone has any idea of how I can handle this?
Thanks!
| Date created |
| 2020-03-02 12:14:00 |
| error |
error = 20/01/2020 11:46
Isn't this what you wanted? The error message text extracted out?
If you also want the details, you can change [Message] to [Detail]. Note that [Detail] is not a string, it is a record.
If your refering to my first post, I think I had a minor bug... just change
"DD-MM-YYYY"to
"DD/MM/YYYY"
10 Replies
It defaults to try MM/DD/YYYY which is the en-us standard.
You will need to transform them manually to get around this.
You mean, to change the date format of every forms in Sharepoint to fr-CA (or en-US)?
Well you might be able to, or you just do it directly in Power Bi like:
= Table.TransformColumns(PreviousStep, {"DateColumn", each Date.FromText(_, if Text.Contains(_, "-") then "YYYY-MM-DD" else "DD-MM-YYYY")})This will need modification based on your:
DateColumn
PreviousStep
Hi NumeroENAP
I can reproduce your problem, if you have "changed type" step, please delete it and open advanced editor,
You could modify your queries as below:
let Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\3\3.13\3.13.xlsx"), null, true), Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]), #"Duplicated Column" = Table.DuplicateColumn(#"Promoted Headers", "date", "date - Copy"), #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "date - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"date - Copy.1", "date - Copy.2"}), #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"date - Copy.1", "date cpoy1"}, {"date - Copy.2", "time copy1"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "BeforeDelimiter", each Text.BeforeDelimiter([date cpoy1], "/")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "BetweenDelimiters", each Text.BetweenDelimiters([date cpoy1], "/", "/")), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "AfterDelimiter", each Text.AfterDelimiter([date cpoy1], "/", {0, RelativePosition.FromEnd})), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "m_d1", each Text.Combine({[AfterDelimiter], [BetweenDelimiters], [BeforeDelimiter]}, "/")), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "m_d2", each Text.Replace([date cpoy1],"-","/")), #"Added Conditional Column" = Table.AddColumn(#"Added Custom4", "Custom", each if Text.Contains([date cpoy1], "-") then [m_d2] else if Text.Contains([date cpoy1], "/") then [m_d1] else null), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"BeforeDelimiter", "BetweenDelimiters", "AfterDelimiter", "m_d1", "m_d2", "date cpoy1"}), #"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "new date time", each Text.Combine({[Custom], [time copy1]}, " "), type text), #"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"time copy1", "Custom"}), #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"new date time", type datetime}}) in #"Changed Type"Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi v-juanli-msft ,
That is the thing : when you extract information from SharePoint, the very first step becomes "Source". Otherwise, I would have just tried to transform it manually.
