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 13, 2022

PaddyB 

Please checked attached in next sheet. I queried sample table in first sheet without manually added columns.

PaddyB's avatar
PaddyB
Brass Contributor
Aug 13, 2022

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

 

  • 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"

     

  • 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

Resources