Date Increment based on Start Date and End Date

[gigotomo]

Hello All,

I have the following Table 

Event	Start Date	End Date
A	1/3/2023 15:35	1/4/2023 17:15
B	1/6/2023 13:10	1/8/2023 16:35

I need to transform this in powerquery,  by incrementing the records if the difference between dates > 24 hrs.

Such that this table would look like this

Event	Start Date	End Date
A	1/3/2023 15:35	1/3/2023 23:59
A	1/4/2023 0:00	1/4/2023 17:15
B	1/6/2023 13:10	1/6/2023 23:59
B	1/7/2023 0:00	1/7/2023 23:59
B	1/8/2023 0:00	1/8/2023 16:35

[v-xinruzhu-msft · Accepted solution]

Hi @gigotomo 

You can refer to the following example

Put the code to Advanced Editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Y3MjAyVjA0tTI2BQuYQAXMrQxNlWJ1opWcwMJmUGFjK0MDsIAFVMAMpDE2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Event = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event", type text}, {"Start Date", type datetime}, {"End Date", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each Duration.Days([End Date]-[Start Date])),
    #"Added Custom4" = Table.AddColumn(#"Added Custom1", "Custom.3", each List.Dates(Date.From([Start Date]),[Custom]+1, #duration(1, 0, 0, 0))),
    #"Expanded Custom.3" = Table.ExpandListColumn(#"Added Custom4", "Custom.3"),
    #"Added Custom" = Table.AddColumn(#"Expanded Custom.3", "Custom.1", each if [Custom.3] >= Date.From([Start Date]) and [Custom.3]<Date.From([End Date]) then [Custom.3] & #time(23,59,0) else [Custom.3]&DateTime.Time([End Date])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Custom.2", each if [Custom.3]=Date.From([Start Date]) then [Start Date] else if [Custom.3]=Date.From([End Date]) then Date.From([End Date]) & #time(0,0,0) else [Custom.3] & #time(0,0,0)),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom2",{"Event", "Start Date", "End Date", "Custom", "Custom.3", "Custom.2", "Custom.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Start Date", "End Date", "Custom", "Custom.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.2", "Start Date"}, {"Custom.1", "End Date"}})
in
    #"Renamed Columns"

Output

Best Regards!

Yolo Zhu

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

[gigotomo]

This worked perfectly... Thanks for the solution

[ppm1]

Please see this video for an approach to handle this scenario.

(3) Simple Model, Simple DAX - Line Time Example - YouTube

This one may help too.

Calculate and Format Durations in DAX – Hoosier BI

Pat