Date / Time manipulation with PowerQuery

[PaddyB]

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 11:58:26 AM

Duration

00:50:42.5186092

I want to to add a new column “StartTime (CEST)” and add there the value from “Start Time (UTC)” + 2hourse to be in “my timezone”

Question here is, how to manage it if I add 2h for example and 11pm? This also needs a change in the date

From this new “StartTime (CEST)” I want to add the Time-Value from the Field “Duration” to get the “EndTime (CEST)”

Here I run in the same situation, what if the duration is over a day-change

alternate i have also already the End-Date wiht UTC datas in it, if this is easier to change then do the math via Duration

Happy to get some input…

thanks

[#2]

@PaddyB The easiest would be to start by setting the data type in PQ, for both start time and duration to a decimal number. Then you can simply add 2 hours (=2/24 of one day) to the start time to get CEST and then add the duration to get the end time CEST.

See attached.

[#3]

how simple :)
was testing and trying for a long time... will give it a try as soon i will be at home this eve.

thaks so much for it

[#4]

@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 

[#5]

@PaddyB Perhaps you first need to transform the Date/Time using US locale. That should transform the date to what your German system recognises. And then you can transfer the date/time to a Number. 

[#6]

@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