Power Query Replaces XLOOKUP?!

[@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.

[@KromboCombo]

What would be the cons to using an index match function?

[@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

[@TonyDiaz-alternate]

You can also load both tables into the data model, create the relationship and bring in the necessary columns.

[@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.

[@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&

[@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.

[@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.

[@rakeshjoshi1425]

Thanks for the video. How is this different from connecting through Data table.

[@thananjeyankandeebanthanap6041]

Please help with a formula for the below,

[@ST-rj8iu]

excellent video!

[@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! 🙂

[@rwfrench66GenX]

Thanks for the video. Does Merge in PQ work the same was as Xlookup if the linked columns have duplicate items and the other columns populate the first rows values over and over?

[@stephenowens8658]

Appreciate options, but when a video title suggest that its better than Xlookup, it would be good to show something that you couldn’t do with Xlookup in a fraction of the time

[@osoriomatucurane9511]

I completly agree with you. Xlookup come to rescue the vlookup as a single Excel lookup function (extract, retrieve records from one table) and it does extremly well, it is widely credited only being surpassed by the filter function. It would be unfair to compare its performance against the super power tools ( Power Query and Power Pivot) which are designed to manipulate and perform data analysis with multiple datasets.

[@alexsforbes3861]

Can I use this to bring in multiple rows of data that might be tied to unique id? If not what would be the best option

[@ajinkyamohite13]

+1 @alexsforbes3861 Please let me know if you have the solution.

[@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.

[@Mr.Nichols]

6 months later, no response. ChatGPT might be our best friend during these moments after all.

[@ojeagadaniel]

You didn't add the all the tables when you used it initially.

[@padrote2865]

Icons should have titles. 😂

[@YanisLa-p7d]

SQL joins