locked
Saving / Calculating formulas in Power Query heavy workbook RRS feed

  • Question

  • I have a 16,000 KB workbook with several queries pulling in from other excel files, our Salesforce data base and SQL database.

    Since adding in the SQL query, the workbook (which also utilizes lots of Sum Product and nested if formulas to look off of those tables being brought in) slows to a crawl when calculating a single sheet. It would be a safe guess to say nearly 30 min for one sheet.

    While it wasn't instantaneous prior to adding the query to our SQL database, it was no longer than a minute. Does anyone have any idea what could be the hold up?

    Thank you in advance.

    Wednesday, May 3, 2017 6:53 PM

Answers

  • So it's Excel formula calculation that has slowed down, not the refreshing of the PQ Queries themselves?

    If that's the case, then this is more of an Excel question. PQ itself shouldn't be affecting Excel formula recalc times.

    Ehren


    Monday, May 8, 2017 6:04 PM

All replies

  • Is the SQL query you added standalone, or is it merged/joined in with the rest of the data?

    Ehren

    Friday, May 5, 2017 8:03 PM
  • Stand alone. I do join that data together within the workbook via lookups, but the query itself just brings in that data.
    Friday, May 5, 2017 8:05 PM
  • Ok. How large is the text of the SQL query you added? (If you're unsure, click Advanced Editor and paste the contents into a text editor to determine its size.)

    Ehren

    Friday, May 5, 2017 8:26 PM
  • I hope I understand your question correctly, but after pasting here are the details.


    Characters 1,402

    Character w/ Spaces 1,554

    Friday, May 5, 2017 8:31 PM
  • Ok, so the size of the query text shouldn't be an issue. How long does refreshing just the SQL query take (i.e. refreshing its results loaded in Excel, via the Workbook Queries Pane, not just the preview in the Query Editor)? Are you seeing a slowdown of your other queries, even when the SQL query isn't being refreshed at all?

    Ehren

    Friday, May 5, 2017 9:06 PM
  • It does take awhile, but not longer than any of the other larger queries. 

    I am seeing a slow down when I calulcate formulas which from understanding, have nothing to with the query at all. I have manual formula calculations checked, so when I attempt to refresh one sheet at a time I see a huge slow down. I am not refreshing any queries when this happens so it seems very odd.

    Friday, May 5, 2017 9:11 PM
  • So it's Excel formula calculation that has slowed down, not the refreshing of the PQ Queries themselves?

    If that's the case, then this is more of an Excel question. PQ itself shouldn't be affecting Excel formula recalc times.

    Ehren


    Monday, May 8, 2017 6:04 PM