Untitled

let
    Source = Excel.Workbook(File.Contents("Z:\CW.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Renamed Columns1" = Table.RenameColumns(#"Promoted Headers",{{"Shipment ID", "ID"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"ID", type text}, {"Shipper's Ref", type text}, {"Origin", type text}, {"Dest.", type text}, {"Carrier", type text}, {"Master Bill", type text}, {"Vessel", type text}, {"Heinz Award", type text}, {"Controlling Region", type text}, {"Order References", type text}, {"ETD", type date}, {"ETA", type date}}),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"Master Bill"}, Carrier, {"MBL"}, "Carrier.1", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=false, Threshold=0.8]),
    #"Expanded Carrier.1" = Table.ExpandTableColumn(#"Merged Queries", "Carrier.1", {"MBL", "ETD", "ETA"}, {"Carrier.1.MBL", "Carrier.1.ETD", "Carrier.1.ETA"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Carrier.1",{{"Carrier.1.MBL", "Carrier_MBL"}, {"Carrier.1.ETD", "Carrier_ETD"}, {"Carrier.1.ETA", "Carrier_ETA"}, {"Master Bill", "MBL"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Carrier_ETD", type date}, {"Carrier_ETA", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Status", each let
    // Function to safely handle a date or return null if there's an error or invalid data
    ValidateDate = (dateValue) =>
        let
            result = try if dateValue is date then dateValue else null otherwise null
        in
            result,

    // Function to calculate the days difference with handling for errors and invalid dates
    CalculateDaysDifference = (startDate as nullable date, endDate as nullable date) as text => 
        if startDate = null or endDate = null then
            "No data"
        else
            let
                duration = Duration.Days(endDate - startDate),
                result = if duration > 0 then 
                            "Delayed by " & Number.ToText(duration) & " days"
                         else if duration < 0 then 
                            "Arriving earlier by " & Number.ToText(-duration) & " days"
                         else 
                            "On Schedule"
            in
                result
in
    CalculateDaysDifference(ValidateDate([ETA]), ValidateDate([Carrier_ETA]))),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"ID"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Duplicates",{{"Status", "ETA status"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns2",{"ID", "Shipper's Ref", "Origin", "Dest.", "Carrier", "MBL", "Vessel", "ETD", "ATD", "ETA", "Heinz Award", "Controlling Region", "Order References", "Carrier_MBL", "Carrier_ETD", "Carrier_ETA", "ETA status"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"ATD", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom", each let
    // Function to safely handle a date or return null if there's an error or invalid data
    ValidateDate = (dateValue) =>
        let
            result = try if dateValue is date then dateValue else null otherwise null
        in
            result,

    // Function to determine the sailing status based on ATD, ETD, and Carrier_ETD
    DetermineSailingStatus = (atd as nullable date, etd as nullable date, carrier_etd as nullable date) as text =>
        if ValidateDate(atd) <> null then
            "Sailed"
        else
            let
                validETD = ValidateDate(etd),
                validCarrierETD = ValidateDate(carrier_etd),
                duration = if validETD <> null and validCarrierETD <> null then Duration.Days(validCarrierETD - validETD) else null,
                result = if duration = null then 
                            "No data"
                         else if duration > 0 then 
                            "Delayed by " & Number.ToText(duration) & " days"
                         else if duration < 0 then 
                            "Sailing earlier by " & Number.ToText(-duration) & " days"
                         else 
                            "On Schedule"
            in
                result
in
    DetermineSailingStatus([ATD], [ETD], [Carrier_ETD])),
    #"Renamed Columns3" = Table.RenameColumns(#"Added Custom1",{{"Custom", "ETD_status"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns3",{"ID", "Shipper's Ref", "Origin", "Dest.", "Carrier", "MBL", "Vessel", "ETD", "ATD", "ETA", "Heinz Award", "Controlling Region", "Order References", "Carrier_MBL", "Carrier_ETD", "ETD_status", "Carrier_ETA", "ETA status"})
in
    #"Reordered Columns1"