Merge Tables without VLOOKUPS | Power Query 101 | Easy Excel Tutorials

[@excelwithpaolo]

This is the first video in my Power Query 101 series. If you’d like to see more, please like, subscribe, and leave a comment below.

[@mrt6874]

thanks a lot; could you add a link to the/a playlist with all of them to the video description?

[@exercise2773]

Hi Paolo, thanks for very well explained tutorial. Using Vlookup you can map a score to a letter grade by ensuring the lookup table is sorted and leveraging non-exact match. How do you achieve the same look up results using merge in Power Query? Assuming u have table with upper and lower bounds columns and a letter grade column. Thanks

[@excelwithpaolo]

Thanks. I'm not quite sure, but you may want to look into lookups with multiple criteria. In my "VLOOKUP with multiple lookup values" video, I also work through an advanced merge with multiple criteria in the second half of the video: ua-cam.com/video/knDFK4XtldM/v-deo.html
Another thing you may want to look into is "fuzzy" matching in Power Query.
Hope this helps.

[@Palmakify]

Hi, I have an interesting Power Query Data cleaning problem.
I have not been able to figure it out. Are you interested in taking a look?

[@excelwithpaolo]

I’m working on a data cleaning video. What would you like to see?

[@Palmakify]

@@excelwithpaolo Thanks for responding, I will do my best to explain.
So I have extracted data from a retailer website and used a programme to write this data into Excel. The issue I have, is that for every SKU (product) I scraped, the product details such as Metal Type, Stone Type, Stone Weight etc. Would come in different order from one SKU to another.
Example:
SKU A | Metal Type | Steel | Stone Type | Emerald | Stone Weight | 0.75ct
SKU B | Stone Type | Emerald | Stone Weight | 0.75ct | Metal Type | Steel
SKU C | Stone Weight | 0.75ct | Metal Type | Steel | Stone Type | Emerald
SKU D | Metal Type | Steel | Stone Type | Emerald | Stone Weight | 0.75ct
SKU E | Metal Type | Steel | Stone Weight | 0.75ct | Stone Type | Emerald
SKU F | Metal Type | Steel | Stone Type | Emerald | Stone Weight | 0.75ct
The data extracted from the website comes to me very messy.
Ideally for all of the SKUs, Metal Type, Stone Type, Stone Weight would be in the same order.
SKU A | Metal Type | Steel | Stone Type | Emerald | Stone Weight | 0.75ct
SKU B | Metal Type | Steel | Stone Type | Emerald | Stone Weight | 0.75ct
SKU C | Metal Type | Steel | Stone Type | Emerald | Stone Weight | 0.75ct
SKU D | Metal Type | Steel | Stone Type | Emerald | Stone Weight | 0.75ct
SKU E | Metal Type | Steel | Stone Type | Emerald | Stone Weight | 0.75ct
SKU F | Metal Type | Steel | Stone Type | Emerald | Stone Weight | 0.75ct

[@excelwithpaolo]

You could try using the unpivot feature in Power Query to change the format of the data from wide to narrow. From there you can sort it in the preferred order and then switch back to wide data if needed…

[@jeremynorbury]

@@Palmakify /r/excel

[@mrt6874]

thanks a lot, i always hated this vlookup function

[@iqbalmu]

Subscribed

[@excelwithpaolo]

Thanks for the support!