VLOOKUP / XLOOKUP in Power Query
Вставка
- Опубліковано 29 сер 2024
- In this video, you will learn how to perform a VLOOKUP/XLOOKUP in Power Query. The reason to use Power Query is to perform the lookup dynamically, extremely fast, and way more easily.
If you are performing a lookup on large datasets, VLOOKUP/XLOOKUP would be dead slow as it would need to check cell by cell for an exact match but with Power Query, it takes just a couple of mins and steps and you're done :)
#VLOOKUP #XLOOKUP #INDEX&MATCH #POWERQUERY
Thank you
Thank you for the video. We know that vlookup will return the first value when there are multiple values with the same ID, how do you handle ths in power query?
You should get your answer here. The last part is about how to combine multiple matches in Power Query
ua-cam.com/video/DLnH_vUez-c/v-deo.html
Thanks! But any idea why I often get so many duplicates?
Thanks for the post. Both of my connection tables are fine but I get many errors in a column of data after I merge the two tables. The error says "We couldn't convert to Number." Do you know why? I converted that column to text in the table I'm drawing that column from.
Hello, get rid of the "Changed Type" step in the Power Query, most probably, the columns have mixed data (text & numbers) and that's the reason the type step is creating those errors.
@@ExcelwithAG Thank you! That fixed my problem!!!
@@petecullan3113 Great and thank you for the feedback, cheers!
Thanks for the great video, I have a scenerio where Table 1 has two key columns and Table two has 1 key column. I want to merge them like lookup Table1[column1] or Table[column2] and match with Table2[column1].. Is this possible in power query?
I tried but it ask for two columns to match from table2 which i dont have.
If you have any solution can you help
Create a single key column in Table 1 as well while doing the transformation before loading as connection only. Select both the columns, right-click and merge. You will have your original data as it is and a unique column to use as a lookup key in power query loaded as connection only. Once done, do the merge.
@@ExcelwithAG Thanks for the response, I will try this