+ Reply to Thread
Results 1 to 9 of 9

How can I delete the differences between 2 tables using PQ?

  1. #1
    Forum Contributor
    Join Date
    11-22-2020
    Location
    Sydney Australia
    MS-Off Ver
    365
    Posts
    198

    How can I delete the differences between 2 tables using PQ?

    Hi All,

    With the assistance of Ali a couple of weeks ago, she created me a PQ table based upon the data in an input table. The input data grows daily. The PQ looks at the input data , finds records based upon a minimum time value, and creates an output table. Works a treat.

    However, I am finding the input table is growing rapidly. Because I only need the minimum time records, all other records in the input table are of no value.

    Ideally, to speed things up, I would like to delete records from the input table that are not a minimum time value.

    Essentially, compare the input table with the output table and delete the differences.

    I am looking for some guidance because what I have tried is either unsuccessful or just to slow. PQ is so much faster.

    I have attached a sample workbook.

    Any assistance would be great.
    Attached Files Attached Files

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,888

    Re: How can I delete the differences between 2 tables using PQ?

    I don't understand why you would need to remove data from the the input when the output is what you are desiring and getting. Just because the table gets larger is no reason to delete data. At some point you may wish to use that data for some other analysis that is unknown today.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    11-22-2020
    Location
    Sydney Australia
    MS-Off Ver
    365
    Posts
    198

    Re: How can I delete the differences between 2 tables using PQ?

    Hi Alan, thanks for the quick reply.

    The only data I will ever require are the records that have minimum time values. This input table grows daily by hundreds of records. The issue is that I have several queries all based on the input table. Once the minimum time value records are identified, all other query data is based upon the min time value.

    The bigger the input data table, the longer it takes to get query results. This is only one part of a large model, consequently, I am looking to speed up the processing time where ever possible.

    Cheers

  4. #4
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,888

    Re: How can I delete the differences between 2 tables using PQ?

    How will you determine which data in your source file is to be deleted in the source. It will have to happen before you run your query.

  5. #5
    Forum Contributor
    Join Date
    11-22-2020
    Location
    Sydney Australia
    MS-Off Ver
    365
    Posts
    198

    Re: How can I delete the differences between 2 tables using PQ?

    Ah.....good point Alan...

  6. #6
    Forum Contributor
    Join Date
    11-22-2020
    Location
    Sydney Australia
    MS-Off Ver
    365
    Posts
    198

    Re: How can I delete the differences between 2 tables using PQ?

    can i subtract the output result table from the input table? That would leave only the min time value records in the input table ready for the next days new records to be added to the existing min time records in the input table.

    This is process needs to be done every day. Reason being that one day there will be a new min time record that will replace the old min time record.

  7. #7
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,888

    Re: How can I delete the differences between 2 tables using PQ?

    because you only want to keep the minimum in your source data, I think that you may want to try using the Minifs function and filter out your unwanted data. This may be more effective than PQ. You will need to list your variables that you want to filter on. Not being a Horse racing fan, I am not knowledgeable as to which items need to be your criteria, but I think that it would be Distance, Class and Course. You would then need to filter for each combination.

    https://exceljet.net/excel-functions...inifs-function

  8. #8
    Forum Contributor
    Join Date
    11-22-2020
    Location
    Sydney Australia
    MS-Off Ver
    365
    Posts
    198

    Re: How can I delete the differences between 2 tables using PQ?

    I will look at this suggestion over the weekend. Thanks.

  9. #9
    Forum Contributor
    Join Date
    11-22-2020
    Location
    Sydney Australia
    MS-Off Ver
    365
    Posts
    198

    Re: How can I delete the differences between 2 tables using PQ?

    Hi Alan, thank you for questioning my thought process. I have found that not only did I find the solution, but also that in source data there are hundreds of duplicate records that can also be deleted. I would never have found them without you questioning me.

    Thanks Again.

+ 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] Compare of 2 tables & find the differences
    By Regina HR in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-20-2020, 06:49 AM
  2. [SOLVED] Highlight differences in 2 tables with data on different rows
    By Reefaman in forum Excel General
    Replies: 4
    Last Post: 07-06-2017, 01:41 PM
  3. Find differences between 2 excel sheets or tables
    By FBG098 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-24-2015, 11:16 AM
  4. How Compare two tables, and result ( only the differences )
    By Chrystopher in forum Excel General
    Replies: 4
    Last Post: 02-20-2015, 10:20 AM
  5. [SOLVED] Excel 2007 : List differences between tables
    By Yathish.d in forum Excel General
    Replies: 6
    Last Post: 07-29-2012, 12:19 AM
  6. Excel 2007 : Merging tables to look and point out differences
    By windstorm2453 in forum Excel General
    Replies: 0
    Last Post: 12-01-2011, 10:22 AM
  7. Compare tables and mark differences (vlookup?)
    By goodhope in forum Excel General
    Replies: 6
    Last Post: 03-25-2011, 04:55 PM

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