Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
hungry_learner
Frequent Visitor

Merge multiple excel data into one single table based on date

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.

2 ACCEPTED SOLUTIONS
johnt75
Super User
Super User

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.

View solution in original post

v-yajiewan-msft
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
v-yajiewan-msft
Community Support
Community Support

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
Super User
Super User

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!