Merge multiple excel data into one single table based on date

Create a new query as List.Max(Actuals[Date]) then add a filter to Forecast so that it only retrieves rows where Date is >= the new query you created.

Hi hungry_learner , hello johnt75, thank you for your prompt reply!

Please import two Excel files and name them Sheet1 and Sheet2. Then, create a new blank query to paste in:

let
    // Load data from the 'Actual' file
    ActualSource = Sheet1,

    // Ensure the Date column data type is set to date for further calculations
   Actual = Table.TransformColumnTypes(ActualSource, {{"Id", Int64.Type}, {"Name", type text}, {"Date", type date}, {"Amount", type number}}),

    // Get the maximum date in the 'Actual' table
    MaxActualDate = List.Max(Actual[Date]),

    // Load data from the 'Forecast' file
    ForecastSource = Sheet2,

    // Ensure the Date column data type is set to date
    Forecast = Table.TransformColumnTypes(ForecastSource, {{"Id", Int64.Type}, {"Name", type text}, {"Date", type date}, {"Amount", type number}}),

    // Filter 'Forecast' data to only include dates greater than MaxActualDate
    FilteredForecast = Table.SelectRows(Forecast, each [Date] > MaxActualDate),

    // Combine 'Actual' and filtered 'Forecast' tables
    CombinedTable = Table.Combine({Actual, FilteredForecast})
in
    CombinedTable

Best regards,

Joyce

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