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
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Aug 15, 2022

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.

 

  • 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
    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Aug 15, 2022

      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. 

  • PaddyB's avatar
    PaddyB
    Brass Contributor
    Aug 15, 2022
    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

Resources