Create new line based on condition

Hi leandrovieira ,

 

Maybe you can try this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDSNzTTNzIwMjq0QElH6dACBUMTKwMDCNPICM40sAAyQbxYHWyakFWaIpjmME2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Init = _t, End = _t, Dif = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Init", type time}, {"End", type time}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "start time", each 
    if [End]<[Init] 
        then {#datetime(Date.Year([Date]),Date.Month([Date]),Date.Day([Date]),Time.Hour([Init]),0,0),#datetime(Date.Year([Date]),Date.Month([Date]),Date.Day([Date])+1,0,0,0)} 
        else {#datetime(Date.Year([Date]),Date.Month([Date]),Date.Day([Date]),Time.Hour([Init]),0,0)}),
    #"Expanded start time" = Table.ExpandListColumn(#"Added Custom", "start time"),
    #"Added Custom1" = Table.AddColumn(#"Expanded start time", "end time", each 
    if [End]<[Init]  and Date.Day([start time]) <> Date.Day([Date]) 
        then #datetime(Date.Year([Date]),Date.Month([Date]),Date.Day([Date])+1,Time.Hour([End]),0,0) 
        else if [End]<[Init] and Date.Day([start time]) = Date.Day([Date]) 
            then #datetime(Date.Year([Date]),Date.Month([Date]),Date.Day([Date]),23,59,59) 
            else #datetime(Date.Year([Date]),Date.Month([Date]),Date.Day([Date]),Time.Hour([End]),0,0)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "New date", each DateTime.Date([start time])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "New Init", each DateTime.Time([start time])),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "New End", each DateTime.Time([end time])),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "New diff", each [New End]-[New Init]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom5",{"Date", "Init", "End", "Dif", "start time", "end time"})
in
    #"Removed Columns"

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

 

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