none
Table.Join/Merge in Power Query takes extremly long time to process for big tables RRS feed

  • Question

  • Hi,

    I tried to simply merge/inner join two big tables(one has 300,000+ rows after filtering and the other has 30,000+ rows after filtering) in PQ. However, for this simple join operation, PQ took at least 10 minutes (I killed the Query Editor after 10 minutes' processing) to load the preview.
    Here's how I did the join job: I first loaded tables into the workbook, then did the filtering for each table and at last, used the merge function to do the join based on a same field.
    Did I do anything wrong here? Or is there any way to improve the load efficiency?
    P.S. no custom SQL was used during the process. I was hoping the so called "Query Folding" can help speed the process, but it seems it didn't work here.

    Thanks.

    Regards,

    Qilong

    Tuesday, January 13, 2015 12:38 PM

Answers

  • Hi,

    I've already solved the problem by consolidating load, filter and merge step together using M language.  Now the preview can be loaded in seconds.

    Thanks for help.

    Regards,


    • Edited by Qilong L Wednesday, January 14, 2015 5:24 AM
    • Marked as answer by Qilong L Wednesday, January 14, 2015 5:24 AM
    Wednesday, January 14, 2015 5:24 AM

All replies

  • Hi!

    You should import the source tables in Access. This will speed up the work of PQ in several times.
    Tuesday, January 13, 2015 6:22 PM
  • It's hard to analyze your query without seeing it. If you don't feel comfortable posting it here, consider using "Send a Frown" to send the query contents to our feedback address.
    Tuesday, January 13, 2015 10:02 PM
  • Hi,

    I've already solved the problem by consolidating load, filter and merge step together using M language.  Now the preview can be loaded in seconds.

    Thanks for help.

    Regards,


    • Edited by Qilong L Wednesday, January 14, 2015 5:24 AM
    • Marked as answer by Qilong L Wednesday, January 14, 2015 5:24 AM
    Wednesday, January 14, 2015 5:24 AM
  • Hi, are you able to provide details to how you achieved a faster load? I am experiencing the same issue now.. Thanks
    Monday, February 29, 2016 8:55 PM