Power Query: Extract City Name from Description with List.Accumulate function Excel Magic Trick 1840

Learn about how to extract the city name from a description using Power Query with the List.Accumulate function and a Custom Function.
Topics:
1. (00:00) Introduction
2. (00:05) What we did last video
3. (00:38)) Excel Worksheet REDUCE function to help visualize what happens in Power Query
4. (02:54) Power Query Finished Formula List.Accumulate and Custom Function
5. (04:01) Build List.Accumulate and Custom Function formula
6. (06:14) Formula for more than one city in description
7. (07:11) Summary
8. (07:33) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #lookup #xlookup #powerquery #poowerbi #extraction #records #clean #datacleaning #datacleansing

[viktorasgolubevas]

Superb, Mike!
Advanced techniques via accumulation in minutes!

[barttitulaerexcelbart]

Oh Mike, this was a great trick but I still believe difficult for the average Excel user. Since I belong to this group I propose another solution: Just Merge the two tables, yes just merge, and then use the magic: "Use Fuzzy Logic" . At your first try you will NOT have a success, but guess what: set the "Threshold" to 0, 1 !! Awesome.!! Greetings Bart....😉

[NarendraSunku]

You are my Excel Guru ❤
This video content is awesome as always.

[SyedMuzammilMahasanShahi]

Thank you so much for this EXCELlent share Amazing Mike.

[wmfexcel]

List.Accumulate! What a magical function!

Honestly, it's not a simple function that can be mastered easily. Although I see many other (including this one) videos solving complicated problem with this function with ease, I am still unable to build this function from scratch by myself. 😅😅

By the way, I would suggest a minor addition to the M-code to make it case-insensitive.

Text.Contains([Description], a, Comparer.OrdinalIgnoreCase)

[johnborg]

Thanks Mike!! In my opinion, formulas are better, but this was very interesting!!!

[chrism]

Love the M Code solution, thanks Mike!

[ricos]

Yes! I like it Mike. Very well explained.

Edit: I'd maybe have put a character in the split between the duplicates though, just so you can then split into columns quickly if necessary. Or perhaps even generate a list? Otherwise, magnificent.

[M.HanifK]

Thanks. Your formula works like a charm. I am not getting why ISNUMBER is used. What is the logic behind this? Everything is text

[som-yf]

I would like to request a seperate playlist on Excel that covers all the tabs (also all options present inside it) and dialogue box options in the program.

As a student, I am eager to learn all about Excel and how to use it effectively. A separate playlist covering all the features of Excel would be a valuable resource for me and other students.

[davidkoo]

Wow... Al have to revist this to truly get it....was able to solve it with pandas but not really as elegant

[cassiusclaudius]

How did you turn “Cities” into a list, and then bring it into the Query?

[spilledgraphics]

M function: List.Accumulate & Master coincidence these two start with magic letter: M ? 🔥