Looking for better ways to update excel master file from a daily new product export

[EstherPhilippen]

My first step in techcommunity, hi!

Situation
We use a cloud based business application with very limited product catalog. There is no API or other way to access the source data, there only is a excel export of the product table and the export file contains a unique product ID. The product table contains 30.000 rows now, and growing yearly with <10.000 rows.
I enriched a first product table export file with new columns/fields for additional product information, and use this as a Master product file now. Updating this Master with the daily application export file, is a struggle and I'm quite sure there is a better way to do this.
We haven't migrated to M365 yet, and are using Free Teams for Business (and so Sharepoint with very limited storage capacity). I work with Windows10 & Office2019 Pro.

How I work now

New rows: I pull a daily product table export out of the online application, compare that file on the ID column with the ID column of the master file, to find all #N/A which are the new rows in the export file. I cut/paste these new rows manually to add them to the master file.

Updated rows: for all columns that are in the export I recorded a macro that replaces each column in the master file with the columns from the file of that day, through a match/index function on each column that is in the export file.

Issues & inefficiency

It's a delicate construction to use for further data lookups & enrichment (sorting, filtering, querying & writing): functions or macro need to be rebuild on a regular base.

I daily replace ALL values of the master with the export file values of that day, <5% of the values changes daily.

What I am looking for?

Two things actually:

To improve working method with limited tools & knowledge as is.

Best practice to reach easy and reliable update of master, considered I/we will have M365 Business in the near future... I need to lobby for tools and can use some help ;-))

Oh, by the way... I'm exploring PowerQuery, but don't see "how to" in my use case yet

[#2 · Accepted solution]

@EstherPhilippen Difficult to recommend based on imagination only, but I'll give it a try.

Power Query would definitely be the tool to use. Take your current master file and merge it (Left anti join) with the update file. That will leave you with all rows from the master that are NOT in the update file. That is, the 95% that didn't change. Now, you append the update file to the "95% master" to create a new master containing all old, updated and new rows. No VBA or complicated formulae needed.

[#3]

@Riny_van_Eekelen Thank you! 

Indeed, hard imaging but it turned out to be even easier, as the system export contains all records with current values, so I didn't need to extract rows that didn't change. I only had to left outer join the system export file with the master, to create a new master in which I could delete all previous system columns.

I guess tomorrow I only need to replace the system export file and refresh the query :)