Lookup the biggest data

[Mars3442]

Hi everyone

Please help me to solve this problem

I have two tables

The first table contains Master Data

Store	Customer Product Code	Product Name	Company Product Code	Description Product
A	199876	Food A 200G	20001	Food A 200G
A	199876	Food A 195G	20002	Food A 195G
A	199876	Food A 180G	20003	Food A 180G
B	298651	Food A 200G	20001	Food A 200G
B	298651	Food A 195G	20002	Food A 195G
B	298651	Food A 180G	20003	Food A 180G
C	199876	Food A 200G	20001	Food A 200G
C	199876	Food A 195G	20002	Food A 195G
C	199876	Food A 180G	20003	Food A 180G

The second table from the customer

Store	Month	Customer Product Code	Product Name
A	1	199876	Food A 200G
A	1	199876	Food A 200G
A	2	199876	Food A 195G
A	3	199876	Food A 180G
B	1	298651	Food A 200G
B	1	298651	Food A 200G
B	2	298651	Food A 195G
B	2	298651	Food A 195G
B	2	298651	Food A 195G
B	3	298651	Food A 180G
C	1	199876	Food A 200G
C	2	199876	Food A 195G
C	3	199876	Food A 180G
C	3	199876	Food A 180G

My expectation table

Store	Month	Customer Product Code	Product Name	Company Product Code
A	1	199876	Food A 200G	20001
A	1	199876	Food A 200G	20001
A	2	199876	Food A 195G	20002
A	3	199876	Food A 180G	20003
B	1	298651	Food A 200G	20001
B	1	298651	Food A 200G	20001
B	2	298651	Food A 195G	20002
B	2	298651	Food A 195G	20002
B	2	298651	Food A 195G	20002
B	3	298651	Food A 180G	20003
C	1	199876	Food A 200G	20001
C	2	199876	Food A 195G	20002
C	3	199876	Food A 180G	20003
C	3	199876	Food A 180G	20003

Can I create the expected table with power query in excel?

And how to do that?

Thank you

[Arpitb12 · Accepted solution]

Open your Excel workbook with the two tables

Go to the Data tab in Excel.
Click on Get Data and select Combine Queries and then Merge.
Select "Inner" for the join type

Try this

[Arpitb12 · Accepted solution]

Open your Excel workbook with the two tables

Go to the Data tab in Excel.
Click on Get Data and select Combine Queries and then Merge.
Select "Inner" for the join type

Try this

[Mars3442]

Sorry maybe I forgot to submit some data

I have a Master Data like this

STORE	MONTH	YEAR	PRODUCT CODE FROM CUSTOMER	DESC CUSTOMER	COMPANY PRODUCT CODE
XXX	M01	2023	2053166	PRODUCT A 30 G	427661
XXX	M03	2023	2053166	PRODUCT A 30 G	422615
XXX	M01	2023	2903079	PRODUCT A 150 G	403617
XXX	M03	2023	2903079	PRODUCT A 150 G	428207
XXX	M07	2023	2903079	PRODUCT A 150 G	430380

My expected table

STORE	MONTH	YEAR	PRODUCT CODE FROM CUSTOMER	DESC CUSTOMER	COMPANY PRODUCT CODE
XXX	M01	2023	2053166	PRODUCT A 30 G	427661
XXX	M02	2023	2053166	PRODUCT A 30 G	427661
XXX	M03	2023	2053166	PRODUCT A 30 G	422615
XXX	M04	2023	2053166	PRODUCT A 30 G	422615
XXX	M05	2023	2053166	PRODUCT A 30 G	422615
XXX	M06	2023	2053166	PRODUCT A 30 G	422615
XXX	M07	2023	2053166	PRODUCT A 30 G	422615
XXX	M08	2023	2053166	PRODUCT A 30 G	422615
XXX	M01	2023	2903079	PRODUCT A 150 G	403617
XXX	M02	2023	2903079	PRODUCT A 150 G	403617
XXX	M03	2023	2903079	PRODUCT A 150 G	428207
XXX	M04	2023	2903079	PRODUCT A 150 G	428207
XXX	M05	2023	2903079	PRODUCT A 150 G	428207
XXX	M06	2023	2903079	PRODUCT A 150 G	428207
XXX	M07	2023	2903079	PRODUCT A 150 G	430380
XXX	M08	2023	2903079	PRODUCT A 150 G	430380

what should I do in power query if I want to lookup the Company product code like the expected table?

Because 1 Customer product code has some Company product code in a specific month

thank you

[v-cgao-msft · Accepted solution]

Hi @Mars3442 ,

Please try use merge queries in power query:

Merge queries overview - Power Query | Microsoft Learn
Merge queries (Power Query) - Microsoft Support

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum