Forum Discussion

ExcelBot0815's avatar
ExcelBot0815
Regular Visitor
6 years ago
Solved

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

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 selli...
Need Help
  • ExcelBot0815's avatar
    ExcelBot0815
    6 years ago

    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.

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
ExcelBot0815
Regular Visitor
6 years ago

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's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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