Forum Discussion
Power Pivot: Need help population information from one row to another
So after thinking about the second part of your comment once more I came up with the following solution. Please let me know whether you think it's correct or not:
I merged the two queries to the two data bases (company A and company B) and did an "Inner join" via GTIN (so I kept only the corresponding/matching rows). Then I kept the columns "GTIN" and both article numbers and removed the rest. Then I removed duplicates on the column "article number A" in order to have a unique identifier that I can connect to my main table. So via this table and the established connection via "article number A" I am now able to do "=related('tableInnerJoinofTable1andTable2'[article number B])" and now I have the article number next to the pallets as well.
Community ChampionNot sure I understand totally. How is the system supposed to know that the missing GTIN for item 3 Article A 10000123 is the same and should relate to 55500001 with 1,000 units. I get the 1,000 unit part, but what makes article b and article A relate.
What if you created your own table of all GTIN codes and article numbers and populated them with Power Query where they were blank from the source? I always prefer to create tables like this vs doing some smart logic that breaks down later.
If you can explain the first part for me, or consider the second, let me know and I'll see where I can help.
edhans:Thanks for your quick reply.
I think the problem you have with understanding the matter is exactly why it's not working at the moment (in a data base sense it's probably not very logical what I want to do because ID 3 in fact doesn't have a match via any key).
So regarding the first part of your answer:
You are correct, the two data sets are in fact not related to each other. Hence I am trying to relate them. The logic behind it should be, that once I know that article number "10000123" is related to article number "55500001" (which I know from IDs 1 and 2), I want to check whether the combination of article number "10000123" (or rather 55500001 from ID 1 and/or ID 2) plus the quantity unit "1000" (from ID 3) is know in table 2. However I am missing the information "55500001" on ID 3. My initial question was meant to be: How do I get that piece of information to the row of ID 3? Because once I have that piece of information I can simply put a new key there which is a combination of the foreign article number plus the quantity. With said key I could then check whether the pallet is the "same".
Regarding the second part:
I am open for a solution that is reproducable (in like 5-15 minutes). That is beacause I need to do this comparison of company A with company B like 20 times (of the course of a couple of months) and on different data bases (though they are all formatted the same).
Best regards