CODEWITHSUNDEEP
exceldata-modelingpowerpivot
Chris
Chris

Reputation: 249

How to connect data in Excel Power Pivot data model with no unique identifier

I am trying to build an Excel Power Pivot data model using restaurant inspection data from my city, though I'm having trouble envisioning how to get this to work properly. I have three files I've imported into the data model but cannot figure out how to link:

  1. business_lookup; each entry is unique with a business ID number, a business name and address.
  2. inspection_lookup; each entry is distinct inspection on a specific date for a specific restaurant but has no unique identifier. It does not distinguish how many violations were found on this visit, just that a visit occured.
  3. violations; a file full of each individual violation found on each of the inspection_lookup dates. It has the business ID, date and a description of the individual specific violation. For each inspection in the inspection_lookup, there are typically multiple corresponding violations in this table.

The problem, to my understanding, is that there's no unique field like "inspection_ID" that could link the inspection_lookup file to each of its many findings in the violations file to allow me to say on June 5, 2020, Jim's Fish House had three violations and they were X, Y and Z. I can connect both to the business_lookup file easily enough, but I can't figure out how to link these other two tables. How can I connect these two other files when all I know is that the unique business ID was inspected on a common date?

Upvotes: 0

Views: 478

Answers (1)

Michael Korotkov
Michael Korotkov

Reputation: 313

If in inspection_lookup you have date and specific restaurant (I assume it corresponds to business ID or business name), you can create unique key by concatenating these 2 columns (given you cannot have more than 1 inspection on the same day in the same restaurant). You can create the same unique key in violations and connect these 2 tables. Business_lookup has unique values so you can connect it to violations or inspection_lookup based on your use case.

Upvotes: 1

Related Questions