Power Pivot: Need help population information from one row to another

[ExcelBot0815]

Hello everyone,

so my power query/power pivot skills are rather mediocre. Here's the situation:

I am comparing the items one company (A) is selling with the items another company (B) is selling. Usually such an item has a selling unit for the customer (quantity = 1), a packaging unit (quantity unit e.g. 10) and a pallet (quantity unit e.g. 1000). However in most cases only the selling unit and the packaging unit have GTINs as references which I can compare directly. The pallet doesn't have a GTIN. However I also need to know whether company A has the same pallets on the item as company B.

So here's my data (heavily simplified of course):

My first table (company A) looks like this:

ID | GTIN | Article number A | Quantity

1  | 1234 | 10000123         | 1

2  | 1235 | 10000123         | 10

3  |      | 10000123         | 1000

My second table (company B) looks like this:

GTIN | Article number B | Quantity

1234 | 55500001         | 1

1235 | 55500001         | 10

9999 | 55500001         | 1000

Now in the power pivot data model I connect the two tables via the key "GTIN". Then I insert a new column into the first table with "related(...[Article Number B]). Now the result I get is:

ID | GTIN | Article number A | Quantity | related article number B 

1  | 1234 | 10000123         | 1        | 55500001

2  | 1235 | 10000123         | 10       | 55500001

3  |      | 10000123         | 1000     | 

Now I know that company B is also selling the item. I also know that they are working with the same packaging unit (10). 

But I don't know whether the pallets match, because company A isn't storing the "GTIN" on pallet level.

So I am looking for a smart way to also get the information on "ID = 3" that the related "article number b" is also 55500001. Which is true, because in my case although "ID = 3" (in the first and third table) is missing the "GTIN" it has the same quantity and refers to the same "customer unit".

So I was thinking (from a classic Excel approach) to populate the related "article number B" onto each row that has the same "article number A" (10000123) and then cross check again with the combination of the newly matched "article number B" (55500001) and the quantity (1000) whether that combination is available in table 2 (which it is).

However I am stuck... How do I get the related article number B in the third row in order to check whether that combination is contained in table2?

Please note: This has to happen in the power pivot data model or in the query. I can't modify the data source.

[v-juanli-msft]

Hi @ExcelBot0815 

Could you show me more rows of sample data?

For example, is my sample data correct?

Does "GTIN" in A show all "null" for ID=3, in B show all "9999" ?

Best Regards
Maggie

[ExcelBot0815]

@v-juanli-msft 

Hi Maggie,

part of your data is incorrect.

The ID in Table A is a unique identifier, which has no particular use in the case we are discussing here. So in your table it should be IDs 1-6. Also per definition the GTIN (in the retail area) is kind of unique. Per definition it identifies a unique retail item or it's packaging unit. So while you can have the same GTIN on ID 1 and 4 (because they both relate to 1) you can't have the same GTIN on ID 2 and 5 (because the quantities don't match).

To answer your other, more specific question: GTIN in A for "pallets" will always be null. GTIN in B again follows the definition above. So on ID 3 it would be 9999 and on ID 6 it would be something else (e.g. 9998 or 4321).

Please also note that I have a more or less working solution via an inner join that I described in message 3 of this thread.

However, if you have a solution how to solve this without another join and relate function I am very happy to hear it.

Best regards

[v-juanli-msft]

Hi @ExcelBot0815 

Sorry, i'm not clear about your data.

I don't know how to relate two tables, based on GTIN or Quantity?

Best Regards
Maggie

[edhans]

Not 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. 

[ExcelBot0815 · Accepted solution]

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.

[ExcelBot0815]

@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