Lookup A Value in a Query Table

[andrew58695]

Hello,

I am trying to use lookup a value and check if it exist in a large dataset (~1 million lines). Obviously, I can use vlookup, but even if I lookup the value in another excel file it tends to slow things down quite a bit and I anticipate the file will grow to be quite large. I've been taking some online classes on Excel and was hoping to use power query to accomplish this much easier (assuming my very basic understanding of it is correct).

Basically I have a table in Excel with a column that needs to check if value "123456" (also in the excel table) exists in the much larger dataset, and have it return yes/no. This table will continue to grow, as will the larger dataset. Easy stuff? My experience here is not very in depth but I am hoping to learn more.

Help/pointers appreciated.

 

[Matt Allington]

There are lots of layers of complexity in your question. If you have more than 1 million rows of data, how are you managing this in Excel? What are you doing with this information after you validate whether the number is found or not? If you have very large data sets, it may be better for you to use power pivot. How could it will allow you to load millions of rows into a table. You could then link your reference table to the very large table and the relationships that way. But it really depends what it is you are trying to do.

 

[andrew58695]

Sorry if the question was unclear. It may have made more sense in my head lol.

I ended up figuring it out in what I think was the best way. Basically, created a connection only query to the large data set (actually only 20k rows I am not sure where I pulled 1 million from) and kept the column of just the number I needed to look up. Then I added my other smaller table as a query and merged the two queries together, keeping only the matches that were found in the merged/created column. I loaded it to my file directly.

I only need to validate whether the number was found for tracking purposes before further work is done. Now, I can add additional numbers to the large data set when they are released, and refreshing the query will return the values that are found in a new "Match" column that just says yes/no. All the non matches are blank.

Thanks for your help!