Forum Discussion

PaddyB's avatar
PaddyB
Brass Contributor
Aug 13, 2022
Solved

Convert Text to Cell

Hi   i have in a cell a Multiple Line Text - what i want to split in columns    "Start Time (UTC): 8/8/2022 11:58:26 AM End Time (UTC): 8/8/2022 12:49:08 PM Duration: 00:50:42.5186092 [8/8/...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 14, 2022

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

PaddyB's avatar
PaddyB
Brass Contributor
Aug 14, 2022

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"

 

Resources