Circumventing duplicate value in column issue

[plaem]

I have a large database in Access with document names and parts that the documents contain. The lay-out is something like:

• doc 1, part 1
• doc 1, part 2
• doc 1, part 3
• doc 2, part 4
• doc 2, part 5
• doc 3, part 6
• etc

In Excel I have another table with the document name. My goal is to link the table and database, and extract all parts for a certain document name and append it to the Excel table. After that, I need to check if the parts belonging to document X may also be present in any other document Y.
I tried linking the table and database through Powerpivot, however I got an error that, indeed, the column with the document name contains duplicate values. The database is over 40,000 items so adapting the source is not something I'd like to do.
I though about concenating the part numbers for each unique document into one cell, however that would make the subsequent check to see if any part number is in any other document more complicated.

What would be a clever way to go about this task?

 

[Matt Allington]

You need to think differently than with Excel. You should
1. Load the entire table to power pivot (40k is tiny)
2. Load a distinct list of all documents (presumably from Excel)
3. Load a distinct list of all parts (presumably from Access)
4 join the tables.

You can then filter on any document to see all the parts (add part numbers to a pivot table, and count of parts to values. To see the other docs, take a look at my article here. https://exceleratorbi.com.au/show-all-invoices-containing-this-product/

 

[plaem]

I'm a bit lost how to connect the relationships in PowerPivot.
I have a main block from the database with both (non-unique) document and part ID's, made two separate tables with the unique document and part ID's. Then I have my Excel table which contain the partnumbers which should act as input for finding the corresponding documents. I assume the latter can be done with your web article, however I do I properly link the datablocks?

 

[Matt Allington]

I don't know what you mean by datablocks. If you mean tables, then you need to load the tables to the data model and create the relationships from there. There is a lot to learn in Power Pivot, but you can learn it. Maybe my new book would help you.
https://exceleratorbi.com.au/product/supercharge-excel-digital-edition/