Date / Time manipulation with PowerQuery

Question

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

riny_van_eekelen · Answer

PaddyB&nbsp;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.&nbsp;See attached.&nbsp;

paddyb · Answer

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

paddyb · Answer

Riny_van_Eekelen&nbsp;&nbsp;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&nbsp;the error from a sample line (translated)DataFormaError converting to "Number" was not possibleDetails:8/8/2022 11:58:26 AM&nbsp;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&nbsp;

riny_van_eekelen · Answer

PaddyB&nbsp;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.&nbsp;

sergeibaklan · Answer

PaddyB&nbsp;
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] &amp; "+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] &amp; "+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

Forum Discussion

Date / Time manipulation with PowerQuery

5 Replies

Resources