Perform a Lookup with Power Query

[@Arjun-Shah-b2r]

Yes, you are right Doug We can automate the process with Power Pivot also and Data Model

[@DougHExcel]

Thanks for the comment!

[@gcorbalan]

You saved my life! Thank you very much for sharing the knowledge worldwide.

[@DougHExcel]

Happy to help!

[@deedeesdefiance4131]

I'm so glad I watched this. Thanks for saving me several years of my time!

[@DougHExcel]

Hi DeeDees Defiance, glad you liked it, thanks for commenting!

[@rajkumarpanditYouTub]

Very nicely explained

[@DougHExcel]

Thanks!

[@kennethvela2784]

VERY QUICK AND EASY MY FRIEND

[@DougHExcel]

Glad you liked!

[@casst346]

excellent, clear and quick training...thanks..

[@DougHExcel]

Hi mach smith, glad you liked it, thanks for commenting!

[@swilson1958]

Hugely helpful!. I used this on separate worksheets in a single workbook. The difference in file size with lookups (index/match) and power query is ~125,000KB to ~62,000KB -- half the size!

[@DougHExcel]

PQ is fantastic!

[@manojmanol7103]

0

[@johng5295]

Thanks in a million. Great tips.

[@DougHExcel]

Thanks for watching!

[@kassimdhanani140]

Excellent tutorial

[@DougHExcel]

Hi Kassim Dhanani, thanks for the comment!

[@loismathius8747]

Love it!! directly subscribe.

[@DougHExcel]

Yay! Thank you!

[@leelynnyuek4753]

Really useful,thanks for sharing

[@DougHExcel]

Hi leelynn yuek, glad you liked it, thanks for commenting!

[@KelvinAdityo]

Thanks! It's short and clear.

[@DougHExcel]

Thanks for the comment!

[@AtulSingh-kl6tg]

thanks Doug i usually deal with more than 60 mb files daily. it surely make my days .. great work

[@jazzista1967]

Thanks Doug. I did work. I was afraid of deleting the columns because I thought I would affect the actual tables but I realized that this transformation was done behind the scenes . Thanks for clarifying

[@DougHExcel]

Hi jazzista1967, thanks for the comment!

[@TheJoshtheboss]

Awesome stuff. But what if I actually want the EmpID to be added to the original table. Not creating a completely new 3rd table

[@ДаниярБерикович]

Thank you so much Bro!!! I love you!!!!!!

[@DougHExcel]

thanks for the comment!

[@adriancelis9691]

This is great. Thanks!

[@DougHExcel]

Hi Adrian Celis, thanks for the comment!

[@TruthofDilly]

Fantastic thanks

[@DougHExcel]

Thank you too!

[@VS-rh8rq]

As usual great stuff...thanks

[@tonytan7873]

Thank you ! Very useful!

[@messaoudirabah3878]

Very useful, many thanks ...

[@DougHExcel]

You're Welcome!

[@amirhosseintonekaboni4362]

Awesome 👌

[@DougHExcel]

Thank you! Cheers!

[@brman700b]

Hi Dough, its definitely useful for me. But when i do this, the data on table one order will change. The original data has been sort but when the query finish, data is not in order anymore? any mistake or step that i should do?

[@DougHExcel]

On the last step you can click the dropdown in the column to sort it to your liking.

[@MrKamranhaider0]

nice video

[@DougHExcel]

Thanks for the visit!

[@jazzista1967]

Doug: Great example. Question: Lets say you have 2 tables and you only want to connect selected columns from each table: table 1 I have 6 columns and I want to retrieve 3 and from table 2 I have 10 columns and I want to retrieve 5 columns. Can this be done in Power query using this merge feature ? Thanks

[@DougHExcel]

maybe one of these? kzbin.infosearch?view_as=subscriber&query=multiple%20lookup

[@alexmcintosh6921]

It seems the Power Query is only a Viewing Option, is it possible to update the information from within the Query generated Table? If not, is there a way?

[@DougHExcel]

Hi Alex McIntosh, thanks for the comment! maybe this one kzbin.info/www/bejne/rXnKp5KdjNxqn7M

[@zaighamuddinfarooqui1705]

Nice video.

[@DougHExcel]

Thanks!

[@DanielLamarche]

Great tutorial Doug. Thanks. Just a question, we *had* to convert the list into an Excel table before starting the process right? Asking because I couldn't see the small blue marker at the bottom right of list identifying them as Excel ... Tables. Gonna stick with your tuts for a while.

[@DougHExcel]

You're welcome! Yep... Power Query takes the input as a table, though if you invoke power query it will do that step anyways.

[@AladineTK]

Thanks for sharing ❤️

[@DougHExcel]

You're Welcome!

[@deepakverma7636]

One question... I often use power query merge function for lookup the tables.. but i noticed it distorted the order of left table.. i mean to say if i have a column and i want lookup values from another table but in order of that column only.. it often not in that order

[@DougHExcel]

Hi deepak verma, thanks for the comment. Maybe adjust the sort order then do the merge?

[@deepakverma7636]

@@DougHExcel no, that is what i dnt want to do ..because i want to keep that order only .. sorting will change the row records order..

[@DougHExcel]

@@deepakverma7636 try adding an index column to keep that order and after sort on that index

[@zafrazf]

pls can you help on one thing, all steps followed and working perfectly, but when merging with relationships, if data in one column is in lowercase/mix and the other column with the data are in uppercase/mix, it is not working (I understand u can change to lowercase and uppercase), when the data are mixed, it is not working. But if u use vlookup for the same data, it is working.

[@DougHExcel]

case sensitive search vids covered here kzbin.info/www/bejne/lXXRfqh5rJV8r7M kzbin.info/www/bejne/j6m9kGtjq9h4pqc kzbin.info/www/bejne/Y5KrZ6Csh855Zsk

[@canefan17]

I pulled in my data via "Import from Folder." I loaded the query as a table. Now I want to be able to delete rows or add things to the table, but I want to do this at the table and not in power query editor. Problem is every time I've tried this, I'll delete the rows, and then if I hit refresh... the rows come right back. Is there any way to edit the table/query from the table and not power query editor?

[@DougHExcel]

Hmm, that would negate the use of PQ, if you this is your intention, then you'd just have to edit the source table manually first and then bring into PQ.

[@armsotopre]

thanks for the video.

[@DougHExcel]

Thanks Armando Soto!

[@userme2803]

Hi thanks for your excellent video. I want to ask you a question, excel 2010 under Data-->from other resources-->from ms query-->from excel. What is the difference between that option and power Query? Thank you.

[@DougHExcel]

I'm not an expert on this but as far as I know MS Query is the older querying tool that lets you query from databases (oracle or access) or flat file data sources (Excel or csv). Power Query is more advance as you can query from these type of sources and in addition big data sources like HADOOP, Azure, and even something like Facebook.

[@Muuip]

Great presentation and very useful info, many thanks! This is like a VLOOKUP exact match, can it also be done for non-exact match?

[@DougHExcel]

Thanks, Power Query can do an approximate match...may be a video in the future :-)

[@wilsonsantiago39]

tks 4 yr time n knowledge

[@DougHExcel]

You're welcome!

[@aries284769]

How can the file be shared or transferred when the connection to the external data is established and data model created?

[@DougHExcel]

if the external connection is accessible from your local network to all or it's to a website that PQ can pull from yes.

[@SannareddyHarshavardhan547]

Thank You !!

[@DougHExcel]

Thanks for the comment!

[@parameshtadari9245]

Nice

[@DougHExcel]

Hi paramesh t, thanks for the comment!

[@pankajkaushik12]

Hi Doug- I have a list of sentences written in a column, I want to highlight only capital letter in some particuler color. pls help

[@DougHExcel]

Find & replace with the advance portion might give you some insight. kzbin.info/www/bejne/oqrUqXlsf5drqM0

[@Dopeboyz789]

Can you match a letter or number in multiple columns or rows?

[@DougHExcel]

maybe one of these vids kzbin.infosearch?query=multiple+lookup

[@tradepbskarachi7202]

if some row is not matched where it goes.thanks.

[@DougHExcel]

it's a null entry

[@ahmedal-dossary4386]

Amazing..!!

[@DougHExcel]

Hi Ahmed Al-Dossary, glad you liked it, thanks for commenting!

[@khaledenbaya6446]

Thank you

[@DougHExcel]

You're Welcome!

[@LISETTEHAURY]

amazing

[@denysss5872]

I found that PQ is case sensitive, and DON'T work like vlookup in Excel. For example: "Daf" and "DAF" in Excel is the same, unlike PQ: if in the right table record is "Daf", and in the left table is "DAF" MERGE return null. Another terrible thing in PQ: if in the left table we have some matching records with different target values, merge will return all of them in the new rows

[@DougHExcel]

Yep. PQ is case sensitive :-| but merge does have a couple options...see kzbin.info/www/bejne/fZ2wd3WEhrqEh5o

[@denysss5872]

Doug H i start from that video. And there's no solution for case sensitive records. Try yourself

[@DougHExcel]

Case sensitivity is unfortunately a known issue with PQ. There is the workaround described at the MSFT tech forum social.technet.microsoft.com/Forums/en-US/f12496b0-7424-44e7-a384-a08694595899/power-query-load-not-maintaining-case-sensitive-data?forum=powerquery or conversion of values to UPPER | LOWERCASE (thought this is powerbi, it can apply to PQ) -> community.powerbi.com/t5/Desktop/Merge-not-Finding-Matches-Against-Custom-Column/td-p/103486

[@garvensmichel5559]

If it's two millions ID's, How to find one???

[@mickaeltaieb6161]

What good is Power Query if loading a large data set causes Excel to crash? Analyzing 1M rows through pivot tables is very slow.

[@DougHExcel]

Mickael Taieb, that is very interesting...thanks for letting me know.

[@felipepinilla9896]

Awesome

[@DougHExcel]

Thanks Felipe Pinilla!

[@hockeystuff8662]

PQ is awesome, no doubt. However, many articles on the web indicate that Merge is equivalent to VLOOKUP (at least "0" exact match), but they are not, regardless of join type. This video ignores this point when suggesting reasons for the use of one or the other. VLOOKUP will only ever return the same number of values as there are instances of the formula. In other words, it will not add rows (records) to the table containing the VLOOKUP formulas. Wheareas duplicates in both tables being merged certainly will. The simplification of this illustration avoids this by have no duplicates in the "Lookup" table containing the columns (fields) ID and EmplID. Adding a transformation step to remove duplicates before or after merge, provides a different result than VLOOKUP.

[@DougHExcel]

Hi Hockey Stuff, thanks for the comment!

[@julysky100]

Not sure, what I was wrong, it does not work

[@alialbayati9368]

it seems similar to Microsoft Access.

[@DougHExcel]

Hi Ali Albayati, thanks for the comment!