Custom Function in Power Query to Merge 2 Tables

/in /by

Custom Functions in Excel’s Power Query. It’s finally time to take them on. For a while I couldn’t think of a good use. It’d seen the how-to but never really a what-for.

In this video I propose a common situation where lists will trickle in and we’ll need to convert the state abbreviations into state names via a master list. E.g., convert LA to Louisiana, and GU to Guam.

We’ll get one list, then 2 hours later another list, then tomorrow we’ll get 2 lists. Ongoing trickle. We could write a new VLOOKUP every time, but that would get tedious.

A Custom Function in Power Query will help us out.

I show how to do this as a partially manual process with 2 steps:
1. Manually bring the data into Power Query
2. Apply the custom function.

Pretty simple, but it takes some thinking.
You’ll see some M Code and the “Insert Step After” feature.

#PowerQueryCustomFunction
#CustomFunctionInPowerQuery
#MergeTablesInPowerQuery

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Download the workbook:
http://datascopic.net/CFMerge
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2