Forum Discussion
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:
- Not all sales order have a corresponding fulfillment number.
- the order numbers between sales and fulfillment do not match.
- Only fields used to match with the keys are customer number, part number, and transaction date.
- 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.
1 Reply
- mussaendaCommunity Champion