Convert Text to Cell

SergeiBaklan 

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"

PaddyB 

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

SergeiBaklan 

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