This topic contains 10 replies, has 2 voices, and was last updated by 
-
Posts
-
I have two fact tables: Item and Items Purchased which for simplicity will be referred to as Bid. On the item table the MPN matches up to the Item# on the Bid table. Both tables have duplicates. Due to duplicates I cannot create a clean list of items. I have a couple of questions about what I can do with the data.
First off, I did create a clean list of MPNs (by removing duplicates) to use as a bridge table called MPN. Unfortunately, the Bid table has multiple instances of the number along with different actual items. The Bid table also has much fewer MPN numbers. The Item table has three different price/cost fields I need to pull into a document. For simplicity we will call them PriceA, PriceB, and Cost.
As an end result, I need a table that has the item number, description, qty, and id number from the Bid table along with PriceA, PriceB, and Cost from the Item table without summing up any of the information, just pulling the numbers straight across. My relationship is not up to par as I keep getting a large duplicate number or running out of memory during processing.
In addition, I keep getting a row of ‘blank’ MPN numbers for items we do not carry with a sum of quantities for items we do not carry when I try to create rows based upon MPN. It also puts all 190,000+ MPNs on the list when I am only needing around 800. In short, this project is a mess.
For some reason I cannot add more than one image.
Final shot
Hi,
Are the duplicate records completely redundant or do they vary slightly?
Roughly, how many records are in the Item and BID tables? (the MPN table has about 190,000+ rows)
Not counting the duplicates, does one MPN in the Item table match up with one-and-only-one Item# record in the BID table? or is there a one-to-many relationship or a true many-to-many relationship between the non-duplicated records of Item and Bid tables?
My first impression is that to create your desired output, Power Query will be the tool to create your list of MPN, Price A, Price B, Cost, Description, Quantity and ID.
Curious also regarding the purpose of the ID column, since you already have an MPN/Item# column?
Have you tried using Power Query to clean up the duplicate records?
Tom
As I said, the data seems to be a mess. Every [MPN] has a [Code] on the item table and the MPN table but not every [Code] has an MPN. The [Item #] on the Bid table is an MPN but not ever [Item #] MPN is found on this Item Table or the MPN table as we may not carry every item. The duplicates on the item table occur both as redundant as well as some having slight variations. The duplicates on the Bid table are frustrating as the MPN is exactly the same but the item description is different as in one MPN might be both for a door lock and for a key but both have different assumed prices and neither are specified on the item table.
The ID from the Bid table is simply a line marker to assist the team with locating the item on the bid. Its usefulness is limited to formatting alone.
My MPN table was created in PowerQuery by pulling from the Item table the Code, desc, and MPN to be used as a bridge table between the two.
I have expanded the MPN to include Code, desc, and MPN and it is working better today. Now I am able to have MPN, Code, and desc as my rows and pull in the necessary quantities and prices I just cannot figure out the best way to handle those falling under blank. The problem is, the quantity is aggregating under ‘blank’ when the item doesn’t exist in the item table and it is not successfully displaying the Item# for those blanks.
Ok.
Since that is where things stand, do you want to keep the records in the BID table that are listing under blank or not?
Yes, I would like to keep the records that are listing under blank. Currently around 400 of the records are being lumped into the blank listing.
Would you like to keep the records under blank still lumped together after creating something like an MPN for them (like having an MPN for “All Others”), or do you need some way of further categorizing records listed under blank?
I would like to keep all MPN’s. Wondering if I can create a combined MPN table pulling from both sources and removing duplicates in order to bridge them together.
An excellent idea! Power Query “Append” to the rescue!
Power Query was a fantastic tool for this. I had fallen into the very common trap of thinking of Power Query only as a means to upload data into PowerPivot. since having this discussion, I have successfully completed this task and trained a coworker on using PowerQuery for this type of work. I have succeeded in saving her hours per day and reduced her workload by nearly a full day per week.
Thank you so very much for your help!
The forum ‘Power Pivot’ is closed to new topics and replies.