Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Mars3442
Helper I
Helper I

Lookup the biggest data

Hi everyone

 

Please help me to solve this problem

I have two tables

The first table contains Master Data

StoreCustomer Product CodeProduct NameCompany Product CodeDescription Product
A199876Food A 200G20001Food A 200G
A199876Food A 195G20002Food A 195G
A199876Food A 180G20003Food A 180G
B298651Food A 200G

20001

Food A 200G
B298651Food A 195G20002Food A 195G
B298651Food A 180G20003Food A 180G
C199876Food A 200G20001Food A 200G
C199876Food A 195G20002Food A 195G
C199876Food A 180G20003Food A 180G

 

The second table from the customer

StoreMonthCustomer Product CodeProduct Name
A1199876Food A 200G
A1199876Food A 200G
A2199876Food A 195G
A3199876Food A 180G

B

1298651Food A 200G
B1298651Food A 200G
B2298651Food A 195G
B2298651Food A 195G
B2298651Food A 195G
B3298651Food A 180G
C1199876Food A 200G
C2199876Food A 195G
C3199876Food A 180G
C3199876Food A 180G

 

My expectation table

StoreMonthCustomer Product CodeProduct NameCompany Product Code
A1199876Food A 200G20001
A1199876Food A 200G20001
A2199876Food A 195G20002
A3199876Food A 180G20003

B

1298651Food A 200G20001
B1298651Food A 200G20001
B2298651Food A 195G20002
B2298651Food A 195G20002
B2298651Food A 195G20002
B3298651Food A 180G20003
C1199876Food A 200G20001
C2199876Food A 195G20002
C3199876Food A 180G20003
C3199876Food A 180G20003

 

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

And how to do that?

 

Thank you

2 ACCEPTED SOLUTIONS
Arpitb12
Helper I
Helper I

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

View solution in original post

Hi @Mars3442 ,

Please try use merge queries in power query:

vcgaomsft_0-1699414013386.png

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

View solution in original post

3 REPLIES 3
Arpitb12
Helper I
Helper I

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

Sorry maybe I forgot to submit some data

 

I have a Master Data like this

STOREMONTHYEARPRODUCT CODE FROM CUSTOMERDESC CUSTOMERCOMPANY PRODUCT CODE
XXXM0120232053166PRODUCT A 30 G427661
XXXM0320232053166PRODUCT A 30 G422615
XXXM0120232903079PRODUCT A 150 G403617
XXXM0320232903079PRODUCT A 150 G428207
XXXM0720232903079PRODUCT A 150 G430380

 

My expected table

STOREMONTHYEARPRODUCT CODE FROM CUSTOMERDESC CUSTOMERCOMPANY PRODUCT CODE
XXXM0120232053166PRODUCT A 30 G427661
XXXM0220232053166PRODUCT A 30 G427661
XXXM0320232053166PRODUCT A 30 G422615
XXXM0420232053166PRODUCT A 30 G422615
XXXM0520232053166PRODUCT A 30 G422615
XXXM0620232053166PRODUCT A 30 G422615
XXXM0720232053166PRODUCT A 30 G422615
XXXM0820232053166PRODUCT A 30 G422615
XXXM0120232903079PRODUCT A 150 G403617
XXXM0220232903079PRODUCT A 150 G403617
XXXM0320232903079PRODUCT A 150 G428207
XXXM0420232903079PRODUCT A 150 G428207
XXXM0520232903079PRODUCT A 150 G428207
XXXM0620232903079PRODUCT A 150 G428207
XXXM0720232903079PRODUCT A 150 G430380
XXXM0820232903079PRODUCT A 150 G430380

 

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:

vcgaomsft_0-1699414013386.png

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors