Merge multiple excel data into one single table based on date

[hungry_learner]

Hi,

Im new to power bi and I'm stuck while creating a report.

So basically I have two excels 'Actual' & 'Forecast'. 

Actual: Has Id, Name,Date, Amount columns which only has dates till current or previous month

Forecast: has id, Name, Date, Amount for whole year since all those are forecasted values.

Now what I want help with is I want to create a table using this two excels where id, name will remain same but I need data till max date from 'Actual' file and from 'Forecast' file I need data greater than max date from 'Actual' whatever it is be it current month or previous month.

The output table should be 

Id, Name, Date(till max from 'Actual' and greater than max of actual from 'Forecast') Amount

How can I achieve this.

Please note that Actual files are coming every month so each file will have data for that particular month so I'm consolidating it all into one single file which would have Actuals till date.

Any help would be appreciated.

Thank you.

[johnt75 · Accepted solution]

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.

[v-yajiewan-msft · Accepted solution]

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.

[johnt75 · Accepted solution]

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.