Power Query Replaces XLOOKUP?!

In this video, we share how Power Query can replace XLOOKUP when merging data in Excel. Looking for our full tutorial on Power Query? Watch it here: • This Excel Feature Sav... @ExcelCampus **JOIN OUR FREE TRAINING SESSION ** excelcampus.com/blueprint #shorts #excel

[@mozgus79ify]

Also with xlookup the result will refresh automatically after adding rows to the tables . With power query you need to Refresh the query

[@ExcelCampus]

Great point! There are pros & cons for this technique. I'm not saying to use Power Query over XLOOKUP all the time, and I'm sorry I didn't mention that in the video. It really depends on the scenario. I talk more about that in this longer video kzbin.info/www/bejne/bWS3aJypg7t7ldk And this one. kzbin.info/www/bejne/fnPKomOClpyXoJY Thanks! 🙂

[@harumih.3727]

@@ExcelCampus Hi Jon, I have completed your Pro Power Query course recently, and I find PQ is more versatile and more usable than XLOOKUP. XLOOKUP can take less time for one purpose, or maybe a couple of purposes, depending on what a user wants to do with the table. Using PQ and creating "reference" we can use it for many purposes of analyses, such as a finding of anomalies (I often use this analysis), and grouping-by, etc. I agree with the opinions of pro and con, and I agree with you, but for me to do various analyses on multiple datasets, Power Query is the first step to create a query and a reference, so that later, I can change criteria on the query quickly and effectively.

[@smplfy]

You could make this even more complicated by adding those miniscule data sets to the data model, joining them via "create relationship" limited to one-to-one cardinality, then creating a pivot table to analyze them. Personally, I'll stick XLOOKUP.

[@onearmedfarmer5393]

Hi Jon, Thank you for the short form video of this feature, I appreciate your brevity 👍 I am looking for a way to utilize power query to merge data from multiple work sheets that are contained within the same work book. Do you have another video that describes this? Thank you🙂

[@ExcelCampus]

Great question! 😀 It is a longer process than in this video, but very doable. I have a video as part of my Power Query playlist that I will link here for you. I hope it helps! 😊 kzbin.info/www/bejne/fpTJfIKrqLR9nqM&

[@gdecome]

I love Power Query and Merge is very helpful but not always the best solution if XLOOKUP is an option. Also, important to note merge, different from XLOOKUP, will return multiple occurrences if tables are 1:N

[@thananjeyankandeebanthanap6041]

Please help with a formula for the below,

[@stuff5392]

Great content, as always. My one complaint since delving into Power Query (at your suggestion btw) is that MS really needs to give PQ a refresh. It's..for lack of a better word, kludgy. I use similar tools with much better, more streamlined features.

[@robpeacock9031]

Greetings, Jon, and Happy New Year to you! Jon, I have been a subscriber for a LONG time and always enjoy your videos and tutorials. However, I'm sorry, but I just don't like KZbin's Video Shorts feature! I would have preferred that you spoke either for an additional 2 seconds, or more slowly, so that this video plays "full screen"! Of course, it could also be a case of me not knowing how to get there!

[@ExcelCampus]

Thanks for the feedback, Rob! We have been doing a mix of long form and short form videos on KZbin as shorts are very popular. With that said, it can be a challenge to fit training into 60 seconds. We are testing different types of content with shorts to see what our audience likes. We will still continue to produce the long form, and I completely understand if that is your preference. Thanks again and have a good one! 🙂

[@Cloversmom]

Does this only work if the tables are on the same sheet? I'm trying to combine tables on two different sheets and the PQ window only shows the table from the sheet I'm on. When I click Merge and then the dropdown to select the table to merge with, there are no options listed.

[@codywines7867]

Wouldn’t XLOOKUP take a fraction of the time with the desired result?

[@ExcelCampus]

Great question, Cody! It definitely can. And I'm not saying to always use Power Query over XLOOKUP. It definitely depends on the scenario. If you are using Power Query to bring data into Excel and prepare it, then you can also use the merge feature to perform lookups and create relationships between tables at the same time. I wasn't able to cover all that in this short video, but I do explain the pros & cons in more detail in this longer video. kzbin.info/www/bejne/bWS3aJypg7t7ldk I hope that helps. Thanks again and have a good one! 🙂

[@codywines7867]

@@ExcelCampus Better to have more than one path up the mountain

[@209_Violate]

@@codywines7867 power query gets better with data magnitude. definitely something you want in your arsenal.