Forum Discussion
Lookup the biggest data
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
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 typeTry this
Hi Mars3442 ,
Please try use merge queries in power query:Merge queries overview - Power Query | Microsoft Learn
Merge queries (Power Query) - Microsoft SupportBest Regards,
Gao
Community Support TeamIf 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
3 Replies
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 typeTry this
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
Hi Mars3442 ,
Please try use merge queries in power query:Merge queries overview - Power Query | Microsoft Learn
Merge queries (Power Query) - Microsoft SupportBest Regards,
Gao
Community Support TeamIf 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
