Power Query Excel date and timezone

[MartijnB]

Hi,

I have a question about two columns (start_time and end_time).
Both columns have a 'date, 'time' and 'timezone'.
I want to calculate the columns into the right time in a extra column.
Example:

When you look at the first row
Start_time = 10-6-2023 12:30:00 +02:00
End_time = 10-6-2023 18:00:00 + 02:00

This is what I want:
Start_time = 14:30:00
End_time = 20:00:00
Third column = 05:30:00 (hours between the start and end time)

Sometimes the end_time date is greater then the start_time date. Example:
Start_time = 24-11-2022 23:00:00 +01:00
End_time = 25-11-2022 03:00:00 +01:00

What I want:
Start_time = 24:00:00 or 00:00:00
End_time = 04:00:00
Third column = 04:00:00 (hours between the start and end time)


Hope this is possible.
Martijn

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    fnDateTimeFrom = (dt as text) as datetime => DateTime.From(Text.BeforeDelimiter(dt, " +"), "en-GB") + Duration.From(Text.AfterDelimiter(dt, "+")),
    tbl = Table.TransformColumns(Source, {{"start_time", fnDateTimeFrom}, {"end_time", fnDateTimeFrom}}),
    tbl1 = Table.AddColumn(tbl, "duration", each [end_time]-[start_time]),
    Result = Table.TransformColumnTypes(tbl1,{{"start_time", type time}, {"end_time", type time}, {"duration", type duration}})
in
    Result

Book2
	A	B	C	D	E	F	G
1	start_time	end_time		start_time	end_time	duration
2	10-6-23 12:30:00 +02:00	10-6-23 18:00:00 +02:00		14:30:00	20:00:00	0.05:30:00
3	11-2-23 23:00:00 +01:00	12-2-23 00:00:00 +01:00		00:00:00	01:00:00	0.01:00:00
4	12-6-23 22:00:00 +00:00	13-6-23 04:00:00 +00:00		22:00:00	04:00:00	0.06:00:00
5
Sheet2

 

[MartijnB]

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    fnDateTimeFrom = (dt as text) as datetime => DateTime.From(Text.BeforeDelimiter(dt, " +"), "en-GB") + Duration.From(Text.AfterDelimiter(dt, "+")),
    tbl = Table.TransformColumns(Source, {{"start_time", fnDateTimeFrom}, {"end_time", fnDateTimeFrom}}),
    tbl1 = Table.AddColumn(tbl, "duration", each [end_time]-[start_time]),
    Result = Table.TransformColumnTypes(tbl1,{{"start_time", type time}, {"end_time", type time}, {"duration", type duration}})
in
    Result

Book2
	A	B	C	D	E	F	G
1	start_time	end_time		start_time	end_time	duration
2	10-6-23 12:30:00 +02:00	10-6-23 18:00:00 +02:00		14:30:00	20:00:00	0.05:30:00
3	11-2-23 23:00:00 +01:00	12-2-23 00:00:00 +01:00		00:00:00	01:00:00	0.01:00:00
4	12-6-23 22:00:00 +00:00	13-6-23 04:00:00 +00:00		22:00:00	04:00:00	0.06:00:00
5
Sheet2

Hi,

Thanks!
Just a question because I'm a beginner with Power Query.
Where should i put the code? In a new column or....

 

[JGordon11]

open a blank query
open the advanced editor
replace the code in there with the code above
change Table4 in the first line to the name of your table

 

[MartijnB]

open a blank query
open the advanced editor
replace the code in there with the code above
change Table4 in the first line to the name of your table

Ok, I get the next error.
Expression.Error: The column from the table has not found.
Details:
start_time

I changed the start_time and date_time column form text into date/time/timezone, see image.
Then I used your code and get the error.