Advice - Creating a list using data from 2 separate files

Hi,
I`m looking for some advice on how to do this in a more time friendly way.

I have .xlsx files each with diff data but in the exact layout & column headers (see screenshot w/ example dummy data). Each file`s data is from a different month. My aim is to ultimately have 1 file with the data as shown in end result (either of those 2 end result layouts I`d be happy with).

Screenshot 2024-04-19 180848.png

Importantly: Any combination (but also obeying below rules) can (but not necessarily) be found in either of the files. The idea is build up historical performance data.

Any idea how this can be done or how best to go about it?

(I don`t think it is but if it`s necessary:

• Landing Page URL and Design ID always equate to each other (eg. Design ID 100 equals www.burger.com)
• A Team ID can be associated with many Location IDs
• Location ID can only be associated with 1 Team ID.
• Team ID can be associated with many Landing Page URLs (& hence Design IDs)...... & VICE VERSA so many Landing Page URLs

)

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Your end result is a picture - I asked you to mock up the end result manually (five rows will do). We cannot manipulate pictures.

And what does 'random numbers for each cell' mean?

Perhaps this will help.
1. Use the following Power Query code to combine the monthly tables:

Please Login or Register  to view this content.

2. Produce a pivot table from the combined table.
Note that when data for a new month is added the range of data for that month will need to be converted into an Excel table and the name of the table should begin with tbl_ and then the month name. (i.e. tbl_May)
Note that once the new table has been named select a cell in the current pivot table, right click the mouse and select refresh all.
Let us know if you have any questions.