Losing data from PowerQuery Merge

SaintEnt 

As variant

Very first time you generate the query which merges empty orders with current date.

With that within that query (not as separate one) you query returned table which gives previous results, merge it with current data in date table, if date table has no data for some fields keep previous data, otherwise keep previous data.

Kind of loop iterating query on itself. With that you keep only one date table updating information in it from time to time and refreshing result.

This part script is

let
    Source = Table.NestedJoin(
        OrderTable,
        {"OrderID"}, Date1Oct,
        {"OrderID"}, "Date1Oct",
        JoinKind.FullOuter
    ),
    #"We dont need initial data" = Table.SelectColumns(
        Source,
        {"OrderID", "Date1Oct"}
    ),
    #"Take data from Date" = Table.ExpandTableColumn(
        #"We dont need initial data",
        "Date1Oct",
        {"Status", "ETA", "PID"},
        {"Status", "ETA", "PID"}
    ),

    #"Get Previous Data" = Excel.CurrentWorkbook(){[Name="FillFromDate"]}[Content],
    #"Merge it with current date" = Table.NestedJoin(
        #"Get Previous Data",
        {"OrderID"}, Date1Oct, {"OrderID"},
        "Previous", JoinKind.LeftOuter
    ),
    #"Expanded Previous data" = Table.ExpandTableColumn(
        #"Merge it with current date",
        "Previous",
        {"Status", "ETA", "PID"},
        {"Previous.Status", "Previous.ETA", "Previous.PID"}
    ),
    #"Combine Status" = Table.AddColumn(
        #"Expanded Previous data",
        "StatusNew",
        each
            if [Status] = null
            then [Previous.Status]
            else [Status]
    ),
    #"Combine ETA" = Table.AddColumn(
        #"Combine Status",
        "ETAnew",
        each
            if [ETA] = null
            then [Previous.ETA]
            else [ETA]
    ),
    #"Combine PID" = Table.AddColumn(
        #"Combine ETA",
        "PIDnew",
        each
            if [PID] = null
            then [Previous.PID]
            else [PID]
    ),
    #"Removed Other Columns" = Table.SelectColumns(
        #"Combine PID",
        {"OrderID", "StatusNew", "ETAnew", "PIDnew"}
    ),
    #"Renamed Columns back" = Table.RenameColumns(
        #"Removed Other Columns",
        {
            {"StatusNew", "Status"},
            {"ETAnew", "ETA"},
            {"PIDnew", "PID"}
        }
    ),
    #"Better to have proper types" = Table.TransformColumnTypes(
        #"Renamed Columns back",
        {
            {"OrderID", Int64.Type},
            {"Status", type text},
            {"ETA", type date},
            {"PID", type text}
        }
    )
in
    #"Better to have proper types"

SaintEnt See attached. Unless I totally misunderstand, you must use the output of the Merged table as the input for the next day. I believe you'll understand what I mean if you look at the file.

So, the OrderTable = Day 1, and the DayTrans table = Day 2

The Merged table = everything from Day 2 plus all from Day 1 if Day 2 contained null.