+ Reply to Thread
Results 1 to 8 of 8

Find all Differences between 2 lists + Power Query

  1. #1
    Forum Contributor
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    104

    Find all Differences between 2 lists + Power Query

    I have (for this example) 2 tables. In each of these tables I have created a key to uniquely identify an employee and a department. Month to month this may change.

    What I would like to be able to do is find all the differences between the two tables. So far I have only been able to find the differences based on how I write the M code. I.E. the differences between June and July. But I also need the changes July versus June. As there may be differences as well.

    I have attached a sample workbook with 2 small tables. They are loaded into PQ and a third table was created to show the differences.
    What I would also like to do is make this dynamic, where I can select which tables I want to compare. I.E. July 2021 v January 2021 ETC. (I think this is done with Parameters within PQ??)

    Any help or guidance would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,543

    Re: Find all Differences between 2 lists + Power Query

    Perhaps this will be of some help.
    1. Produce connections to both the June 2021 and July 2021 tables
    2. Use Left Anti merges to produce the tables of employee and department combinations that are not found in both tables
    Note that a key column is not needed as both the employee and department columns are selected as a key when making the merge.
    This video explains the different types of merges (Left Anti starts at the 17:55 mark): https://www.youtube.com/watch?v=-kle5a7vbRA
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Contributor
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    104

    Re: Find all Differences between 2 lists + Power Query

    Thank you this is helpful.
    What I am ultimately after is a single table (single merge in PQ) that contains both the June v July as well as the July v June.
    Is something like this possible?

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,543

    Re: Find all Differences between 2 lists + Power Query

    That could be accomplished by appending the NotInJune21 and NotInJuly21 tables.
    Let us know if you have any questions.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    104

    Re: Find all Differences between 2 lists + Power Query

    Thank you
    This is, in essence, what I had.
    I was able to find the difference from June v July, it’s the same process to find July v June and then append. I thought there may be a more efficient way to accomplish this.
    I will work on this further, and also work on how I may be able to dynamically compare monthly the tables.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,543

    Re: Find all Differences between 2 lists + Power Query

    I thought there may be a more efficient way to accomplish this.
    Sheet2 displays how this might be accomplished using formulas.
    1. A single table is produced that has columns for Name, Department, Cost Center, Month and then a column that finds discrepancies of names & departments between selected months using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that G1 and I1 contain drop downs.
    2. The output range is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  7. #7
    Forum Contributor
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    104

    Re: Find all Differences between 2 lists + Power Query

    This is an excellent alternative.
    I was stuck in trying to solve with power query.

    Thank you very much for time and for the effort!

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,543

    Re: Find all Differences between 2 lists + Power Query

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Power Query to find last sales date
    By josephteh in forum Excel General
    Replies: 9
    Last Post: 10-17-2020, 06:26 AM
  2. [SOLVED] power query - trying to find combine and load on my version
    By DEEARO in forum Excel General
    Replies: 37
    Last Post: 06-12-2020, 05:49 AM
  3. [SOLVED] Power Query - excel formula translation into Power Query
    By afgi in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 02-19-2020, 03:38 AM
  4. Replies: 4
    Last Post: 02-17-2020, 06:03 AM
  5. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  6. [SOLVED] Power Query - Find and Replace Line Breaks
    By ibuhary in forum Excel General
    Replies: 4
    Last Post: 03-27-2018, 05:33 AM
  7. Replies: 4
    Last Post: 07-09-2007, 04:38 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1