From the course: Excel Power Query Tips and Techniques
Unlock the full course today
Join today to access over 22,600 courses taught by industry experts or purchase this course individually.
Transformation table
From the course: Excel Power Query Tips and Techniques
Transformation table
- [Instructor] Here we've got two datasets that we need to merge. We need to get Adriano's London matched up with the agent Paula and Paula's email address. That would be an easy merge, but notice other pieces of the data. Andrea's Australia really should be Perth, and then New York is in here as NYC and NY, comma, space, NY. We can't cleanly match that. We would have to do some data cleansing or multiple queries. This would be a mess. But what we can do is create a transformation table, and here it is. Now the transformation table is going to be our bridge. It's saying wherever there's Australia, match it up with Perth. Where there's NYC, match it up with New York. And a requirement of the transformation table is that you have a front column and a to column, all right? Let's do it. We've got our queries and connections all ready. We've got the three queries already set up as connection only. Now let's…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Contents
-
-
-
-
-
-
(Locked)
Filtering in Power Query2m 30s
-
(Locked)
Sorting in Power Query1m 17s
-
(Locked)
Pass parameter: Drill down to a single value2m 59s
-
(Locked)
Prevent table from resizing1m 12s
-
(Locked)
Transformation table2m 48s
-
(Locked)
Filter for certain files when importing from a folder2m 11s
-
(Locked)
Warning: Two types of merges3m 18s
-
(Locked)
Splitting columns1m 14s
-
(Locked)