• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Easily Compare Multiple Tables in Power Query

You are here: Home / Power Query / Easily Compare Multiple Tables in Power Query
easily compare multiple tables in power query using list functions
August 5, 2021 by Philip Treacy

Comparing table columns in Excel is a common task. You may need to identify items that are the same, different, or missing from these columns.

In Power Query, table columns are lists and you can compare these lists using table merges. But merging can only be done on two tables at a time. If you need to compare three or more table columns then using List Functions is the way to do it.

It does require some manual M coding and if the thought of that puts you off, it's not hard. And if you never get your hands dirty with some coding, you're going to miss out on the real power of Power Query.

The three examples I'll show you contain only two lines of code each and do what can take multiple lines using table merges or appends.

Watch the Video

Subscribe YouTube

 

Download Sample Excel Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

 

Source Data Tables

I've got four tables. The first contains the names of some imaginary staff members and an ID code assigned to them.

source data tables

The other three tables show the names of staff that attended three different days of training that were arranged for them.

more source data

My tasks are to work out what staff

  • Attended every day of training
  • Attended at least one day
  • Attended no days

I've already loaded three of these tables into PQ so let's load the last one.

Click into the table, Data -> From Table/Range.

get data from table

Then under Close and Load click on the black arrow, Close & Load to and choose Only Create Connection.

close and load

connection only

Who Attended Every Day of Training

I have to find out who appears in every table for Training Days 1, 2 and 3.

If you were using table merges then you'd do an Inner Join on the Training_1 table and the Training_2 table to return only matching rows.

inner join

You then have to do another Inner Join on the result of the first join and the Training_3 table.

If you are eagle eyed you may have noticed that in the Training_2 table the name agueda jonson is lower case. So both of these joins have to be Fuzzy Joins and set to ignore case so that you can do case insensitive comparisons.

You can do all of these steps in 1 line using the List.Intersect function.

First I need to create a new blank query. Right click on the Queries area of the editor and then New Query -> Other Sources -> Blank Query

Name the query Attended All Days.

create new blank power query

If you remember your set theory, Intersect returns whatever is common between sets, or in this case, our Name columns.

Remember that table columns are lists which is why you can use list functions to do this.

Click the Attended All Days query and under Applied Steps click on Source.

select source step

Now in the formula bar type this, and then press Enter.

= List.Intersect( { Training_1[Name], Training_2[Name], Training_3[Name] } , Comparer.OrdinalIgnoreCase )

list intersect

The result is this list of names

list intersect result

What List.Intersect is Doing

I'm passing in two arguments to the function. The first are the columns I want to compare and these are enclosed in { }

The columns are of course the Name columns from the three tables of attendees at each days' training.

The second argument Comparer.OrdinalIgnoreCase tells the function to ignore case in text comparisons. So agueda jonson will look the same as Agueda Jonson.

Filtering the Staff Table

With the list of names of those who attended all days of training, I can use that to filter the Staff table and return all rows associated with those names.

Right-click on the Source step and select Insert Step After. With the new step selected press F2 and rename it to Tab, which is my abbreviation for Table. You can call it what you like really.

insert step after source

With the new step still selected, type this into the formula bar

= Table.SelectRows( Staff , each List.ContainsAny( {[Name]} , Source ) )

So what's happening here?

explanation of table select function

This line of code is doing the following

  1. Select rows from the table
  2. called Staff
  3. where each row
  4. in the Name column
  5. appears in the list Source

If you filter rows by using the menus, Power Query uses the same function. I'm just manually calling it here to do what I want.

The result is the Staff table filtered to just the rows showing those people who attended all days of training.

filtered table

Who Attended At Least 1 Day of Training

This involves checking every day and seeing who was there at least once.

You could do this by combining the three tables (appending the queries) and then removing any duplicates.

Or you could just use the List.Union function

= List.Union( { Training_1[Name], Training_2[Name], Training_3[Name] } , Comparer.OrdinalIgnoreCase )

Using Set Theory again, a Union of the Name columns from the Training_1, Training_2 and Training_3 tables, using case insensitive text comparison because of Comparer.OrdinalIgnoreCase, gives a list of those who attended at least one day.

list union function

If you want to, you can then filter the Staff table exactly the same way as before using the Table.SelectRows function.

list union result

Who Attended No Training Days

To work this out you need to start with the full list of staff and then remove the names of those who attended on each of the 3 days.

Using table merges you can do this with Left Anti Joins, but you need to do 3 separate joins to get the final list.

Or you can do it with in one line with List.Difference

= List.Difference( Staff[Name], List.Union( { Training_1[Name], Training_2[Name], Training_3[Name] } , Comparer.OrdinalIgnoreCase ) )

list difference function

Filtering the Staff table using these names gives

list difference result

Tip - Quick Sanity Check

The number of people who attended at least 1 day (16) added to the number of people who attended no days (3) should equal the total number of staff (19).

Conclusion

I've shown you how to use List functions to compare lists and filter tables. In the sample file I've created I've also included queries that do the same job but use table merges and appends. You can have a look for yourself at the different methods.

If you're not sure about learning M functions like List.Intersect I'd encourage you to read through the Microsoft function definitions to become familiar with them and try them out.

Although I do have a programming background, I didn't wake up one day and know these M functions. I had to put time in to learn what functions existed and how they worked.

When you first started using Excel did you know how to use VLOOKUP or SUMIF? No, you had to learn them. So if you're at all hesitant about learning M functions, just dive in and get started.

easily compare multiple tables in power query using list functions

More Power BI Posts

combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.
fuzzy matching in power query

Fuzzy Matching in Power Query

Use fuzzy matching to compare non-identical text strings and match them together based on how similar one string is to the other.
handling http errors in power query and power bi

Handling HTTP Errors in Power Query and Power BI

Clearly communicate issues with custom messages when dealing with web scraping or API server errors. Download sample Excel and Power BI files
extract characters from strings in power query using text select and text extract

Extract Letters, Numbers, Symbols from Strings in Power Query with Text.Select and Text.Remove

Learn a cool technique to extract or remove letters, numbers and special characters from strings. Sample workbook to download
highlighting data in power bi visuals

Highlighting Data in Power BI Visuals

Learn several techniques to highlight or label important data points in your Power BI visuals. Sample file and code to download.
shape maps in power bi

Shape Maps in Power BI

Shape maps in Power BI can be used to show the distribution of a variable across geographic regions. Learn a trick to plot discrete data too.

Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI

Convert times and durations from decimal numbers to easily understood formats like hh:mm:ss. Sample code and file to download.
sort by column in power bi

Sort-By Columns in Power BI

Create a Sort-By column to allow custom sort order in your Power BI Visuals. Download an example Power BI Desktop file
custom formatting strings in power bi

Custom Formatting Strings in Power BI

Control how data is displayed in Power BI using your own formats. Like hiding negative or zero values, using symbols or custom number formats

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power QueryTag: Power BI
Previous Post:vlookup in power query using list functionsVLOOKUP in Power Query Using List Functions
Next Post:IFERROR in Power Query Using TRY OTHERWISEtry otherwise power query iferror

Reader Interactions

Comments

  1. Rob

    November 25, 2021 at 8:44 pm

    In the output table “attended all courses” the Map Code can remove additional members eg if you make Agueda Jonson lower case in Staffing, it is visible in the Source but then removed in the Map Output. Not figured out how or why yet to resolve/understand, assume it needs “Comparer.OrdinalIgnoreCase” adding into Map?.

    Thanks for this really informative and precise.

    Reply
    • Philip Treacy

      November 26, 2021 at 9:56 am

      Hi Rob,

      Yes I mention using Comparer.OrdinalIgnoreCase in this post.

      Regards

      Phil

      Reply
      • Rob

        November 26, 2021 at 9:31 pm

        Apologies Phil, I meant “Tab”, not map in the Attended All Events Query.

        The Tab code shouldn’t but excludes Agueda Jonson when I make it lower case in the Staff table? “= Table.SelectRows(Staff, each List.ContainsAny ({[Name]}, Source ))”. I don’t understand why and haven’t been able to resolve?

        Reply
        • Philip Treacy

          December 6, 2021 at 4:39 pm

          Hi Rob,

          The Source step creates this list by intersecting the 3 Training tables

          = List.Intersect( { Training_1[Name], Training_2[Name], Training_3[Name] } , Comparer.OrdinalIgnoreCase )

          list intersection

          This is a case insensitive intersection.

          The Tab step then use Table.SelectRows to get the rows in the Staff table where the Name is in this list (created by List.Intersect above)

          agueda jonson isn’t in the Staff table (it’s a case sensitive select) so that row isn’t selected.

          You can select agueda jonson by using Text.Proper around [Name] like this

          = Table.SelectRows( Staff , each List.ContainsAny( {Text.Proper([Name])} , Source ) )

          but you will end up with lower case agueda jonson in your table. That can be fixed by right clicking on the column then Transform -> Capitalize Each Word.

          Regards

          Phil

          Reply
  2. Conrad

    September 11, 2021 at 3:05 am

    This is EXCELLENT!

    Is there a way to compare two tables and find all differences between each in one table?

    In my example I have a listing of employees for June 2021 and July 2021. Is there a way to extract all differences between the two months? So if an employee changed departments from June to July as well as finding employees who were hired in July?

    Reply
    • Mynda Treacy

      September 11, 2021 at 11:52 am

      I expect you’d have to do this as two separate queries. One to compare changes in existing employee records and one to find new employees.

      Reply
  3. Matthias

    August 6, 2021 at 1:15 am

    Hi Phil,
    This was really good. Thanks!
    Could you do more of this?
    I use quite some patterns with List.xyz. There are so many List functions and they are so powerful. You have selected really nice examples and it would be good to see more from you about other List functions you regard as especially useful.

    Your third case is great for the introduction of the important List.Difference. I love M because instead you could also copy the second case and just filter by each not.

    Thanks again,
    Matthias

    Reply
    • Philip Treacy

      August 10, 2021 at 1:57 pm

      Thanks Matthias,

      Yes I plan on doing more posts like this in the near future.

      Regards

      Phil

      Reply
    • chahine atallah

      August 21, 2021 at 2:25 pm

      amazing, i didnt know about these functions, i used to use merge

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x