Two timezone in the same column from the same source

[NumeroENAP]

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

[v-juanli-msft]

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.

[NumeroENAP]

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. 

[artemus]

It defaults to try MM/DD/YYYY which is the en-us standard.

You will need to transform them manually to get around this.

[NumeroENAP]

You mean, to change the date format of every forms in Sharepoint to fr-CA (or en-US)?

[artemus]

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 

[NumeroENAP]

Well, now there's only errors.

Is there a way to extract the text of an "error" cell. Because the very first step (source) is filled with errors.

Thank you

[artemus]

You can, but is that what you want to do?

You can add a new column with:

let val = try [DateColumn]

in if val[HasError] then val[Error][Message] else val[Value]

[NumeroENAP]

Hi @artemus ,

It only gives me "Sorry... We couldn't transform your date as a DateTime value".

[artemus · Accepted solution]

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"

[NumeroENAP]

"If you also want the details, you can change [Message] to [Detail]. Note that [Detail] is not a string, it is a record."

It is perfect @artemus  ! It's working!

The only thing is that the dates that weren't "errors" are still random. But, at this point, I suspect that the problem is SharePoint, and not necessarily the timezone in PowerQuery.