Forum Discussion

PaddyB's avatar
PaddyB
Brass Contributor
Aug 14, 2022

Date / Time manipulation with PowerQuery

I have a few columns in my Excel and try to do some change with PowerQuery. Very new to it and thanks to Sergei for the first steps,   I have the following columns   Start Time (UTC) 8/8/2022 1...
excel
Formulas and Functions
PaddyB's avatar
PaddyB
Brass Contributor
Aug 15, 2022

Riny_van_Eekelen 

 

thank you for your time...

i as trying to "reproduce" your script step by step as i had an error.

i already start struggling at the step of change the type of my source column

= Table.TransformColumnTypes(TeamsMeetings,{{"Start Time (UTC)", type number}})

the Error from a sample line (in german)

DataFormat.Error: Die Konvertierung in "Number" war nicht möglich.
Details:
8/8/2022 11:58:26 AM

 

the error from a sample line (translated)

DataFormaError converting to "Number" was not possible

Details:
8/8/2022 11:58:26 AM

 

the source column, in my case "Start Time (UTC)" is a "Text type". this comes from a previous step. we converted there on "big message block" to this column 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 15, 2022

PaddyB 

As variant, for such source texts

we my shift from UTC to local time zone

let
    Source = Excel.CurrentWorkbook(){[Name="StartEnd"]}[Content],
    Start = Table.AddColumn(
        Source,
        "Start",
        each
            DateTime.From(
            DateTimeZone.FromText( [StartUTC] & "+0:00", [Culture = "en-US"] )
             ),
        type datetime),
    End = Table.AddColumn(Start,
        "End",
        each [Start] + Duration.From( [Duration] ),
        type datetime),
    RemoveUnused = Table.SelectColumns(End,{"Start", "End"})
in
    RemoveUnused

(I'm in UTC+3), but that won't work on Excel for Web and in case of incorrect settings on local computer.

To fix UTC+2 we could

let
    Source = Excel.CurrentWorkbook(){[Name="StartEnd"]}[Content],
    Start = Table.AddColumn(
        Source,
        "Start",
        each
            DateTime.From(
            DateTimeZone.RemoveZone(
            DateTimeZone.SwitchZone(
            DateTimeZone.FromText( [StartUTC] & "+0:00", [Culture = "en-US"] ) , 2)
             ) ),
        type datetime),
    End = Table.AddColumn(Start,
        "End",
        each [Start] + Duration.From( [Duration] ),
        type datetime),
    RemoveUnused = Table.SelectColumns(End,{"Start", "End"})
in
    RemoveUnused

Resources