Split value in column with multiple dates

[jokas10]

Hey guys, i used a code that i found over the forum and it worked perfectly. However its missing one step that i need it to do it. The code that im using is to separate the dates if it changes the dates, and its cutting just over 00h00, but i need the time to be 6am to 6am and not 00h00 to 23h59. The code that its wrking is the following:

Source = AVA.Contents2(),
n1 = Source{[Key="n1"]}[Data],
v12 = n1{[Key="v12"]}[Data],
item08 = v12{[Key="item08"]}[Data],
#"Invoked Functionitem1" = item08(#datetimezone(2023, 7, 1, 0, 0, 0, 0, 44), #datetimezone(2023, 10, 1, 0, 0, 0, 0, 44), "Operational Delays 2", "Embu das Artes"),
#"Changed Type" = Table.TransformColumnTypes(#"Invoked Functionitem1",{{"startTimestamp", type datetimezone}, {"endTimestamp", type datetimezone}, {"reportEventStartTimestamp", type datetimezone}, {"reportEventEndTimestamp", type datetimezone}}),
#"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([reportEventEndTimestamp]), type date),
#"Added Custom1" = Table.AddColumn(#"Inserted Date", "Max Number of Rows", each let
start = Date.From([startTimestamp]),
end = Date.From([endTimestamp]),
days = Duration.Days(end - start)+1
in days, Int64.Type),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Rows", each {1..[Max Number of Rows]}),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Custom2", "Rows"),
#"Changed Type4" = Table.TransformColumnTypes(#"Expanded Dates",{{"Rows", Int64.Type}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type4", "Start Date2", each if [Rows] =1 then [startTimestamp] else DateTime.From(Date.AddDays( Date.From([startTimestamp]), [Rows]-1 )), type datetime),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "End Date 2", each if [Rows] = [Max Number of Rows] then [endTimestamp] else DateTime.From(Number.From(Date.From([Start Date2]))+1-(1/86400)), type date),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Added Custom4",{{"Start Date2", type datetime}, {"End Date 2", type datetime}})



and its returning this: 

[Rickmaurinus]

Jokas,

I don't fully grasp the question. Do you need something like this? 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dcvLCcAgEIThVsKeBfel4rQS7L+NbHIxaAJz+vnmPEk9c8vKagd3aAEbpXeVhtJjNFLoMjXj2a3Lj66felYxcBx81dFFIX3RFcrQtunoBvNdO1RojAs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type datetime}, {"End Date", type datetime}}),
    #"Extracted Year" = Table.TransformColumns(#"Changed Type",
{{"Start Date", each List.Max({ _, DateTime.From( DateTime.Date(_) )+ #duration(0,6,0,0) }), DateTime.Type},
{"End Date", each List.Min({_, DateTime.From( DateTime.Date(_) )+ #duration(0,17,59,59) }  ) , DateTime.Type }})
in
    #"Extracted Year"

Based on the dataset, please give additional clarifying instructions. Perhaps a sample dataset with expected input and output could work. 

Regards,

Rick

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[rubayatyasmin]

Hey, @jokas10 

in custom3 add the #duration and in custom4 subtract the duration. 

for example for custom3 = ...... [Rows]-1 ) + #duration(6,0,0,0)), type datetime),

same for the custom4, just use subtract. custom4 = .......(1/86400)) - #duration(6,0,0,0), type datetime),

See if this works.

refer: https://learn.microsoft.com/en-us/powerquery-m/sharpduration

[jokas10]

Hey my friend, thanks for the response but it didnt work.

It needs to be like:
24/07/2023 09:25:03 ----> 25/07/2023 5:59:59
25/07/2023 06:00:00 ----->26/07/2023 5:59:59
26/07/2023 06:00:00 ----->26/07/2023 13:07:54