cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
davidhinckley Regular Visitor
Regular Visitor

Combining tables on a non-unique column

I'm servicing machines in multiple cities, and need to know how many parts to order for each city based on expected usage. The complication is that some models use parts more frequently than others. So I have two tables:

 

Population

CityModelModel Count
MiamiModel A1
MiamiModel B2
JacksonvilleModel A4
JacksonvilleModel B3

 

Expected Usage

ModelPartExpected Annual Replacement
Model APart 12
Model APart 21
Model BPart 17
Model BPart 22

 

What I need is a result that tells me what to stock:

MiamiPart 116
MiamiPart 25
JacksonvillePart 129
JacksonvillePart 2

10

 

(For example Miami has just one of Model A, which uses two of Part 1 each year, and two of Model B, which uses seven of Part 1. So it needs two of Part 1 to service the Model As, and fourteen of Part 2 to service the Model Bs. So Miami needs a total of sixteen of Part 1.)

 

I could join these tables on the non-unique Model field. That would create duplication, which is what I want in this situation. But Power BI doesn't allow joins on non-unique fields. 

 

I could also create a relationship between each of these and a third table that's just "Model", but I can't seem to get that to work, either.

 

I'm open to doing this in different ways, so long as I end up with the result.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Combining tables on a non-unique column

Hi,

 

You may refer to my solution here.

 

Hope this helps.

 

Untitled.png

1 REPLY 1
Super User
Super User

Re: Combining tables on a non-unique column

Hi,

 

You may refer to my solution here.

 

Hope this helps.

 

Untitled.png