Forum Discussion

Ben_jamin's avatar
Ben_jamin
Frequent Visitor
2 years ago

Need help indexing a key with duplicate values

Hi Power Bi Community,

I am stuck with a problem and need help. I have a flat file (.xlsx file) that has sales data and fulfillment data in one file that is being pulled into Power Bi. I am making the keys as shown below (in Power Bi--minus the bold values). However, with these keys not being unique, it is causing a many to many relationship. 

 

When a key is shown in duplication, I need to start indexing them. This will be based on the min Sales order number being matched with the min fulfillment order (by adding a 1 to the minimum order number and incrementing by 1's from there). 

 

Things to note:

  1. Not all sales order have a corresponding fulfillment number.
  2. the order numbers between sales and fulfillment do not match.
  3. Only fields used to match with the keys are customer number, part number, and transaction date.
  4. Where there is a sales order and fulfillment, there should be a common amount (i.e. 3 sales orders yields 3 fulfillment orders, 5 sales orders yields 5 fulfillment orders, etc)

In conclusion: I am attempting to make the bold values in the keys in the below screenshot. These bold numbers are what I am seeking to create in Power Bi. 

 

 

I hope I have explained this well enough. It's a bit difficult to explain so feel free to ask questions.