Forum Discussion
Convert Text to Cell
Please checked attached in next sheet. I queried sample table in first sheet without manually added columns.
Please checked attached in next sheet. I queried sample table in first sheet without manually added columns.
was running the Query on 1 of my files with 5k entry's - very nice
but i think there is still a small problem.or maybe 2 š
the source files have UTC time-zone, i think in this way also the US-Dateforma. but witht eh query we just "format" this new
see PrintScreen. so, it should be the 4. August 2022 and with he Query we make out if this 8. April
or I'm wrong?
i also have approx 529 errors... maybe connected to the same problem above
very interesting what PQ can do - thank you for showing me
i had to do some changes, because i got after approx. 500 some Erros - something about date
not sure if my changes make any sense š but the error is gone
let Source = Excel.CurrentWorkbook(){[Name="TeamsMeetings"]}[Content], #"Removed Other Columns" = Table.SelectColumns(Source,{"Received UTC", "Sender", "ToRecipient", "ccRecipient", "Subject", "Importance", "Body"}), #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Text.Split([Body], "#(lf)" )), AddStartTime = Table.AddColumn(#"Added Custom", "Start Time (UTC)", each [Custom]{0}), AddEndTime = Table.AddColumn(AddStartTime, "End Time (UTC)", each [Custom]{1}), AddDuration = Table.AddColumn(AddEndTime, "Duration", each [Custom]{2}), #"Removed Columns" = Table.RemoveColumns(AddDuration,{"Custom"}), #"Extracted Text After Delimiter" = Table.TransformColumns(#"Removed Columns", {{"Start Time (UTC)", each Text.AfterDelimiter(_, "Start Time (UTC): "), type text}}), #"Extracted Text After Delimiter1" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"End Time (UTC)", each Text.AfterDelimiter(_, "End Time (UTC): "), type text}}), #"Extracted Text After Delimiter2" = Table.TransformColumns(#"Extracted Text After Delimiter1", {{"Duration", each Text.AfterDelimiter(_, "Duration: "), type text}}), #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text After Delimiter2",{{"End Time (UTC)", type datetime}, {"Start Time (UTC)", type datetime}, {"Duration", type datetime}, {"Received UTC", type datetime}}), #"GeƤnderter Typ mit Gebietsschema" = Table.TransformColumnTypes(#"Extracted Text After Delimiter2", {{"Start Time (UTC)", type text}}, "en-US"), #"GeƤnderter Typ mit Gebietsschema1" = Table.TransformColumnTypes(#"GeƤnderter Typ mit Gebietsschema", {{"End Time (UTC)", type text}}, "en-US"), #"GeƤnderter Typ mit Gebietsschema2" = Table.TransformColumnTypes(#"GeƤnderter Typ mit Gebietsschema", {{"Received UTC", type number}}, "en-US") in #"GeƤnderter Typ mit Gebietsschema1"I see, datetime is in US locale, with sample 8/8/2022 it was hard to understand. To convert from specific locale select column(s), right click, Change type->Using locale
and use here English US
or simply add culture in formula bar