Easily Comparing External Records with Power Query

Easily Comparing External Records with Power Query is what this post is all about, and lately, it is what Excel is all about. Power Query simplifies Excel programming, and Power Query makes comparing records form different data sources a snap.

~ Use Joins Instead of Lookups ~

Comparing Records between files via Power Query

A common request we receive is to see if we can compare the records from two external data files. Record analysis between two or more files is a need many companies have. It is a small simple need. We have the perfect method to easily automate the comparison of two or more data files, to show you just the records you want to see, just how you want to see them. The ability to compare records in Power Query can be fully automated, often without the use of VBA.

Joins Simplify Power Query Programming

You could do this in a Table, using the XLOOKUP Function, but if you do it in Power Query, you will simplify the Excel programming.

In Microsoft Access, you use Joins in your Queries, even though Access has the DLOOKUP Function, that you can use in your query. Joins are superior to onsheet Excel functions, use them.
Power Query is a Relational Database, like Access, SQL Server, Azure, and QuickBooks.

Power Query Join = Table.NestedJoin(PQ_2, {“PROPERTY_ID”, “NO_OF_OWNERS”}, PQ_1, {“PROPERTY_ID”, “NO_OF_OWNERS”}, “09252024PQ”, JoinKind.LeftOuter)

In the image below, you see the results of Joining two CSV Files, in Power Query. Joins are one of the aspects that make Power Query so powerful.

Easily Comparing External Records with Power Query Blog Post: Image of

Different joins, same two tables, give different results. Here we will Filter the Null values, in the Right Table. Thus, new records.

**Power Query Simplifies the Data Transformation Process (** ETL )

You can do it the easy way, or you can do it the hard way. The easiest way is to use Power Query.

Use the right tool for the job; Power Query’s job is to transform your data.

Look at the Transformation Ribbon in Power Query, see below. You can do more here than you can in Excel. This is what Power Query was designed to do.

Power Query has a Transformation Ribbon., Transforming data is what Power Query does.

Try Automating an external data Excel Solution without Power Query:

When it comes to comparing records between two or more files, Power Query makes easy work of it. No functions, no VBA, just the raw external data and Power Query. It is the most efficient method to compare records in two separate, and external data files.

There are all sorts of ways to compare external data files.

What do you want to see? In this case, the client wants to see new clients.

Some of the options are:

Left Outer Join
Right Outer Join
Full Outer Join
Inner Join
Left Anti Join
Right Anti Join
Cross Join

In this example, we used a Left Outer Join, we Filtered to only show records with a Null value in the Right Table. Thus, New Records.

See a Microsoft Website for more info on Power Query Join types for more details on the possible join options. Combining Joins with Filters, gives you added power. Filter to only show Nulls for example. Power Query is a database application.

Joins are Relationships between Excel Tables

What makes databases so powerful is that you can use Joins, between Tables, in your Query. This enables you to grab say the customers mailing address, from the Customer Address Table, which Joins to the Customer Table.

This allows you to normalize your data. As a result you may have two or more “Customer” related Tables. Not everything about the customer goes in a single Customer Table; normalize your data.

Like in Access or SQL Server, you have Queries based on Queries, based on Queries. See diagram below to see the flow of data through the Power Query Solution.

Power Query works like a relational database. Like having a small Access database inside of Excel.

Power Query Programming Services for any Organization

877-392-3539

Comparing Records in Power Query – Example Project

~ Find New Clients ~

We have a new client, that has a common need. They have two, large, external, CSV data files that they want to compare. The client wanted to see all of the records in the new file that are not matching in the old file. It was asked for an automated file, that does not use VBA. A file that allows the user to select designate the two files to compare. Then they Refresh the file, and their work is done.

The Easiest Way to Compare External Data Files

There are lots of ways you can do this. VBA, Functions, or Power Query. For this client, we went with the best method of comparing records, we used Power Query. We avoided the use of macros and functions, and we created a simple and easy to use custom Excel Solution, based on Power Query.

You can do all of this in an hour, maybe two.

Joins are the power behind Power Query. If you want to compare the records in two or more data sources, Power Query with Joins is how it is done.

Setting Up and Running Power Query is Simple

Once you learn how to do this, you can do it in an hour or so. Just setting up the Power Query is less than 45-minutes. A few quick, simple methods, and you have a lean and mean Excel machine.

Easily Comparing External Records in Power Query – Updating the Master File

It is a very quick and easy to update. Anyone can use the file; you do not need to be an Excel expert.

Drop the new 90 MB CSV file into the Data Folder
Open the Master File
Designate the two files to use in the comparison
1. Via DropDown List.
RefreshAll
Review results
1. Excel file size, 1 MB.
Save and close file

That is it, that is how easy it is to run the file.

Setting up the Master Excel file for the Comparison in Power Query

Setting up the Master file did not take much time, a couple hours maybe. It is an automated Excel Template, and it very easy for the user to use. The file is built with the user, and ease of use in mind. How simple could we make it.

Using the file is about as easy as it can get, without using VBA or formulas.

If we add a few formulas, the computer could automatically determine which files to use in the comparison, and as such, all the users would do would be to open the Master File, it would auto-refresh, and the user would simply look at the output of the analysis.
Anything in the folder, that is in the right format, can be used in the analysis.

Easily Comparing External Records with Power Query – Example File

Below are screen shots of the custom Power Query Solution we built for a new client. You can see the simplicity of Power Query. It is the easiest way to compare two or more external data files. We did so without the use of VBA or Excel functions.

User drops the new CSV file in the Designated Folder.

Any file dropped in this folder, in the proper format, can be used.

The user can designate any folder they choose.

Below is the folder, on the network, where the user drops the weekly CSV Data file.

The user selects the csv file in an Excel Table

In the first Table, there are two file selectors. They allow the user to select any file in the list below.

The user can add any file to the second Table, and then Power Query will extract the necessary data, from the designated csv file.

In the image below, you can see one of the tools from ExcelOffTheGrid.Com, EOTG on the Ribbon.

We make it easy on the user, when it comes to selecting the files used.

Easily Comparing External Records with Power Query in Excel 365

Power Query Output as Excel Table

The output you see in the Power Query Table below shows the “New Records” this week, as compared to last week. Very simple, here are the new records.

Excel Tables and Power Query Tables are not the same thing; try refreshing an Excel Table after you have deleted the data.

The Screenshots below show Power Query

You do not need to know what M Code is, to use Power Query. But if you do learn it, the sky is the limit.

Power Query has a nice user interface, making it easy to use.

If you do not know M Code, somethings can be hard to accomplish. I use the fxGetTableParameter from ExcelOffTheGrid.Com

Many of the things that you do onsheet in Excel, you can more easily do in Power Query.

Power Query is like an onion, it has many layers. It starts easy, and grows in complexity from there. Anyone can use Power Query.

Left Outer Join, Filter to Null on Right Table. = New Records.

Power Query is how Dynamic Excel Solutions are built.

Conclusion – Easily Comparing External Records with Power Query

As you have seen, it is relatively easy to compare records from two or more external data files. And as you have seen, it can be fully integrated, fully automated, nothing for the user to do.

Contact Us – We can Assist you with your Power Query Solutions

When it comes to Power Query, there are many layers to the onion. It can take several years to become an expert in Power Query. If you simply want to hire a firm to assist you with all things Power Query, you have found us, Excel and Access, LLC, Power Query programming experts.

Toll-Free: 877.392.3539 | Irvine, California: 949.612.3366 | Manhattan, New York: 646.205.3261

Elizabeth Hand

16:38 15 Jul 21

The team and Excel and Access are very efficient, friendly and professional. I'm confident that when I send a project to them, it will be done quicker than I expected it and done perfectly.Thanks!

BC Synapse

23:32 02 Feb 21

I arranged for two 1.5 hour sessions on Microsoft Excel Tips and Tricks sessions for my organization in January 2021. I am 100% satisfied with the service. Chris and Maria were very responsive to my initial inquiries and helping me with setting up the virtual sessions. This company provide consulting services on various applications and I liked how I was able to customize my sessions by selecting the tips and tricks to be shared- it was 100% customizable.Our trainer, Bob Umlas, was superb in the delivery of both sessions. He provided an Excel workbook and shared his screen to show all the tips and tricks on how to use Excel more efficiently. The sessions were also recorded so that staff could rewatch the lessons again at a later date. Overall I was very please with the company's services and would work with this company again should future opportunities arise.

Christine Williams

20:52 01 Feb 21

These guys are the excel experts! They take our complicated needs and turn them into easy tools that we can use and run on the daily basis. Quick turnaround as well, the latest project we needed help with they completed and had back to me in 24-hours!

John Gebbia

22:45 18 Dec 20

Excel and Access was a great pick for our excel training needs. The training class was very easy to follow and Bob our instructor was very knowledgeable. Would definitely recommend and will be going back to them for future training needs.

Kelly WILKEN

20:01 03 Dec 20

Jacob was fantastic to work with. I was tasked with developing an easy way to track our Corporate Training across all departments and positions based on company requirements and hire date. I knew there had to be a way to do it in Excel with formulas, but it was way above my skill set. I contacted Excel and Access, and they listened to my needs and paired me with Jacob. He was very responsive and was quickly able to come up with precisely what I needed. He was great at making the necessary tweaks, including creating a “GENERATE” button in bright red so that I couldn’t miss it. I can now run all of my reports with all of my different conditions with the click of that big beautiful red GENERATE button. I will definitely reach out to Excel and Access and Jacob for my future needs.

Steven Kurtz

20:12 22 Aug 20

I'm not Pepsi, Amazon or any of their big clients...just a guy with a small, boutique private psychology practice 3,000 miles away in NY. Every time I need these guys they are there...got my project started ASAP, turn around time was amazing and best of all, any and all tweaks were handled ASAP until we got all the bugs out. Admittedly I was afraid we'd settle on a price and I might not be able to make edits. But no worries - each and every edit, debug done with a virtual smile and ASAP. Well worth the money and they won't disappoint. A pleasure to write a positive review for them!

Jerelene Tan

00:18 24 Feb 18

I have engaged Jacob to help me with an Aging Report. He quickly provided me with a solution which I didn't think of. I would highly recommend his service to anyone who needs excel help.

Demetrios Lambrou

03:30 17 Oct 17

I have been assisted once again, by Paul this time, quick and effectively, even though it has been a weekend. So many thanks!!!! Such a great support to Excel users and Excel programmers alike!

Mitch Goins

15:06 01 Aug 17

I had an Excel problem that I could not crack without extreme effort on my part. I contacted Excel and Access via email. I received a prompt response then described my problem and indicated that I really wanted to remunerate them for their (Christopher and Jacob) time and effort. They honed in on my problem description with only 2 very helpful emails that showed me how to solve my problem. I was very impressed and grateful. There was never a mention of any charges. They are consummate professionals. I can see why they are Microsoft MVP's.

Michael Bell

12:22 09 Jun 17

Chris and Paul were amazing. We hit them with a project last minute and they dropped everything and came through within hours. The work was done professionally and exactly as we needed and at a very reasonable price. We were very pleased.

Bret Linford

20:16 30 May 17

Excel and Access, LLC were super responsive. They really went above and beyond my expectations. They were able to customize directly to my needs. The automation they delivered is very fast and easy to use. I'd use them again in a second. AND they're nice people, too. ?

Chris DeSantis

19:10 07 May 17

Our experience with Excel and Access. LLC was excellent. Chris and his team of very skilled professionals were able to help us out immediately. After just a brief discussion of the project we hired them for, they completed the work and exceeded our expectations in just hours. I have used Chris and his team on numerous projects over the span of 10 years. I am consistently impressed with how quick the payback is, and how well his team understands my requirements and delivers a product that exceeds my expectations. His team is always available anytime of day or night to help or answer questions. We highly recommend Excel and Access, LLC for anyone searching for highly effective and easy to use productivity tools.

Jacqueline

13:10 21 Mar 17

I had the pleasure of working with Excel & Access on a very intricate Excel project involving a massive spreadsheet that housed years’ worth of data. After having two other firms take on the project and then bail, I was panicked and desperate to get the project completed in what was now a very tight time frame. I worked with Christopher and Jacob and couldn’t be more delighted with the level of customer service I received. Jacob is extremely knowledgeable, and also incredibly responsive. My e-mails were answered immediately and the project was completed quickly, ahead of my deadline, thanks to his working nights and even over the weekend to get it done. I felt as if I were the only client he was working with at the time! Jacob made a difficult, tedious assignment look like child’s play. Excel & Access are fabulous to work with and are now my go-to for any Excel need, large or small. Good customer service is difficult to come by, but they nail it! If you are looking for help with an Excel project, I recommend Excel & Access without reservation. You won’t be disappointed.