Transform data in PowerQuery instead of using embedded python in PowerQuery

Hi Anonymous ,

If I understood well, this should work:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZDPCoAgDIdfRTwHupmJ3apHqFt06BV6/0OT/hDbikBEPvf525xnO6yb6WxlAQPtYznVLjn0iAZ868vSYLRL9bRr2ietEASE8uRPGzWbZ0e1cypMCgSe3bzYEHR42T1dY5Cd+0xt81874CP7y4Y25j+2nPvMbhSI3JZz34USBrKXHQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Car ID" = _t, #"Task ID" = _t, Type = _t, #"Start time1" = _t, #"Finish time1" = _t]),
    #"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Source,{{"Start time1", type datetime}, {"Finish time1", type datetime}})),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Task ID", "Car ID"}, {{"Rows", each _, type table [Car ID=nullable text, Task ID=nullable text, Type=nullable text, Start time1=nullable text, Finish time1=nullable text]}}),
    #"Align StartDate for Twin" = Table.TransformColumns(
            #"Grouped Rows", 
            {
                {"Rows", (OuterTable)=> Table.AddColumn(OuterTable,"Start time2", (Innertable)=> List.Min(OuterTable[Start time1]), type datetime)}
            }),
    #"Align EndDate for twin" = Table.TransformColumns(
        #"Align StartDate for Twin",
        {
            {"Rows", (OuterTable)=> Table.AddColumn(OuterTable,"Finish time", (Innertable)=> List.Max(OuterTable[Finish time1]), type datetime)}
        }),
    Custom1 = Table.Combine(#"Align EndDate for twin"[Rows]),
    #"Removed Other Columns" = Table.SelectColumns(Custom1,{"Car ID", "Task ID", "Type", "Start time2", "Finish time"}),
    #"Grouped Rows1" = Table.Group(#"Removed Other Columns", {"Car ID"}, {{"Rows", each _, type table [Car ID=nullable text, Task ID=nullable text, Type=nullable text, Start time1=nullable datetime, Finish time1=nullable datetime, Start time=datetime]}}),
    #"Added Index Column" = Table.TransformColumns(#"Grouped Rows1", {{"Rows", each Table.AddIndexColumn(_, "Index",0)}}),
    #"Added Start time" = Table.TransformColumns(#"Added Index Column", 
        {
            {"Rows", each Table.AddColumn(_,
                "Start time",
                (a)=> 
                try
                    if a[Task ID] = [Task ID]{a[Index]-1} 
                    then null
                    else   
                        if a[Start time2] > [Finish time]{a[Index]-1} 
                        then 
                            if a[Start time2] < a[Finish time]
                            then a[Start time2] 
                            else [Finish time]{a[Index]-1}
                        else [Finish time]{a[Index]-1}
                otherwise 
                    a[Start time2])
            }
        }),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Start time", "Rows", {"Task ID", "Type", "Start time2", "Finish time", "Start time"}, {"Task ID", "Type", "Start time2", "Finish time", "Start time"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Rows",{"Car ID", "Task ID", "Type", "Finish time", "Start time"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns1",{"Car ID", "Task ID", "Type", "Start time", "Finish time"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Car ID", type text}, {"Task ID", Int64.Type}, {"Type", type text}, {"Start time", type datetime}, {"Finish time", type datetime}}),
    #"Filled Down" = Table.FillDown(#"Changed Type1",{"Start time"})
in
    #"Filled Down"

 (copy and paste in a blank query) 

Here is some updated DAX for the two columns.

NewStartTime =
VAR vThisStart = CarTasks[Start time]
VAR vThisTaskID = CarTasks[Task ID]
VAR vMinStartThisTask =
    CALCULATE (
        MIN ( CarTasks[Start time] ),
        ALLEXCEPT ( CarTasks, CarTasks[Car ID], CarTasks[Task ID] )
    )
VAR vMaxPrevTaskFinish =
    CALCULATE (
        MAX ( CarTasks[Finish time] ),
        ALLEXCEPT ( CarTasks, CarTasks[Car ID] ),
        CarTasks[Task ID] < vThisTaskID
    )
VAR vMinStartOverall =
    MIN ( vThisStart, vMinStartThisTask )
VAR vResult =
    IF (
        CarTasks[Start time] = CarTasks[Finish time],
        vMaxPrevTaskFinish,
        MAX ( vMaxPrevTaskFinish, vMinStartOverall )
    )
RETURN
    vResult


NewFinishTime =
IF (
    CarTasks[Type] = "T",
    CALCULATE (
        MAX ( CarTasks[Finish time] ),
        ALLEXCEPT ( CarTasks, CarTasks[Car ID], CarTasks[Task ID] )
    ),
    CarTasks[Finish time]
)

Pat