Creating a single unique list, from a similar column is 2 separate tables

I have a Customer Sales table where I have created a concatenated column with 3 separate fields, that I use to join in my model. I have a Customer Inventory table with a similar column with the same 3 concatenated fields. The many to many relationship between Customer and Part Sales, and Customer and Part inventory mean that I need the associative table. I am trying to get a list from both tables, combine them and remove all duplicates. I have been able to get the 2 unique lists from the individual tables but I have not been able to combine these new lists. I do not want to do this in a query, there are over 20 million records in one table and 3 - 4 million in the other

Hi Brad, need some images. Struggling to picture all the moving parts here.

Just re-read and here’s are some initial thoughts (if I’m understanding correctly)

It seems like you’re looking to create a lookup table that can then filter both the sales & inventory tables?

The way you’re describing it seems like the normal way to do it, but yes 20m records will take forever if you are using the remove duplicated feature in the query editor. It won’t be able to handle it.

All I can suggest initially here is to create a table via formula and try the VALUES function. This will return a distinct column of customers. There are other formulas to use if you want more columns of information like SUMMARIZECOLUMNS. (but I would need to see images of tables)

image

image