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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
__BigDaddy
Frequent Visitor

Power Query - Merge table do not search in the entire table.

Hello,

 

After a long look at the web to find a solution. 

I have a small article table (80 rows) and I'd like to enrich it with a big general article table (50 k rows) and send back the information to the excel.
I'm working with power query in excel shared with other people so:

- I cannot just use the relationship

- I cannot insert the 50k rows in the excel to do a index, Match

- I don't want to use power pivot, because it is more complicated to send back data to final users.

 

The power query merge function seems to suit my need very well. 

But it seems to lookup only in the 1000 first rows over 50 k and returns only NULL.
I test with an article in the 1000 1st rows and it works well. 

 

Is there a solution for the merge to work on the entire data table, please ? 

 

Thanks a lot. 

 

 

 

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

Merge query has no such limitation. You must check whether those cells match or not. 

You can do following to test. 

Insert an Index in your lookup table. 

Let's say your matched value is in row 15100 for which you are not getting result. 

Apply a filter on Index for >15000. Now your looked value will be within 1000 rows limit which you are talking about.

So perform merge on this filtered table to establish whether values are matching or not and whether 1000 limit which you are talking about is correct or not.

View solution in original post

3 REPLIES 3
Vijay_A_Verma
Super User
Super User

Merge query has no such limitation. You must check whether those cells match or not. 

You can do following to test. 

Insert an Index in your lookup table. 

Let's say your matched value is in row 15100 for which you are not getting result. 

Apply a filter on Index for >15000. Now your looked value will be within 1000 rows limit which you are talking about.

So perform merge on this filtered table to establish whether values are matching or not and whether 1000 limit which you are talking about is correct or not.

you are right after reaching the data, and past it in notepad I figure out there is space behind it. 
I beleived the "clean" function will prevent this kind of mistake, but it seems the "trim" function only work. 

 

Thanks a lot. 

__BigDaddy
Frequent Visitor

I precise the pivot columns are all cleaned.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.