Description to Attributes with Power Query - Advanced Power Query tutorial

This is a Power Query tutorial that has its origin in real life. It's how to turn a product description into a list of attributes by modifying the filter step to find specific words or word combinations in a larger text with the list of attributes and all synonyms that attribute has in the descriptions. This method utilizes the fact that a filter step in Power Query is a function that is run on every value of the column.

This tutorial was made using Excel 365, but the techniques shown apply to all Excel versions from 2010 onwards.

Chapters:
0:00​ Excel Olympics Animation
0:08 Introduction
4:05 Loading to Power Query
5:05 Unpivoting the Translation Table
5:40 Main Formula and its Explanation
9:22 Final Steps to get the right form of data
11:12 Fuzzy Matching
14:12 Outro

Gear Used:

US Links

EU Links



Disclaimer: Links included in this description might be affiliate links. If you purchase a product or service with the links that I provide, I may receive a small commission with NO additional charge to you! I appreciate your support!

#Excel #ExcelOlympics #gasperkamensek

[pauldfm]

This is fabulous! As a professional data cleanser…I can see so many applications of this in the Master Data Management world. In effect you have created a synonym finder…so many extra options to take this even further to apply standardised abbreviations, recombine into a standardised description where the order is determined by the sequence you set. Of course all MASTER DATA should be in UPPER CASE. Then to improve the descriptions profile the word frequency and add to your synonyms and set a preferred Abbreviation.

[IssueBoyStefan]

Another brilliant one.
Once I've discovered the fuzzy matching last year I was - WOW this one is so handy for dealing with strings.
It's pretty awesome for coding some open end data - giving it according number values - especially if it's a simple ones as brands or something with one or few words.
I believe that this approach you've just shown will be even more helpful in such situations. :)
Thanks again and again, Gasper!

[wayneedmondson]

Hi Gasper. Awesome tutorial! I do something similar but simpler when matching item names in a column with a mapping table using either List.Contains or by doing a merge step. This is definitely more advanced. Looking forward to studying it further. Thanks for sharing the lesson and also the sample data. Thumbs up!!

[sebfromgermany]

Awesome! You’re introducing an excellent range of functions. I wonder why you did not use the fuzzy join function. With the introduced solution you depend on your creativity when filling the attribute table or is there fuzzy logic in the function Text.Contains? Anyway very inspiring 👍👏

[pauldfm]

Hi Gasper - you mentioned a link to the looked on the BLOG - I am probably being dumb but I could not find it...can you add to the notes?

[ahmed-zc]

Briliant :)
I had a similar scenario and it was accomplshed using list.accumulate