Power Query returning results very slowly

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have attached a file with redacted data.

In it are 3 tables which load into power query before producing a single output.


The user selects an account from from the ListBox in sheet MonthlyBalance and reviews output in DataOutput.

There is a transactions table with missing dates where rows are inserted. These are each month-end date from the earliest date to the latest date selected per account

The other tables provide values for calculation, which are meant to populate all values including those in the inserted rows


Ideally I'd like these calcs performed in PowerQuery but even returning what it does so far is so slow, I ended up scapping PQ entirely and redo'ing project in VBA using a single table with helper columns.

I've tried to optimise the steps as much as possible as well as trying Table.Buffer and List.Buffer but no improvement.

This project relates to previous thread Power Query to insert rows? but question here is specifically for performance improvement and example file attached.
Example file

If anyone is able to review the queries in the file and suggest any speed gains, I'd be very grateful :)

TIA,
Jack
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
708
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I noticed a couple of things.
1. Your query Local CCY can be shorter by referencing the first query #"Transactions Filtered by Account". This will prevent PQ running the same query twice.
Code:
let
    Source = #"Transactions Filtered by Account",
    Currency = Source{0}[Currency]
in
    Currency
2. Why are the tables loaded in the data model? Try using connection only without adding to the data model (Seems you are not using it).

Refresh is under 3 counts.
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Thank you @GraH

I'm relatively new to Power Query but have seen that Let and In code structure, though hadn't thought of applying or figured out how/where to insert etc.

All the data input tables (transactions, currency and performance) are manually updated to the sheet, though there will be a data connection at some point.

I wrongly(?) thought loading into the data model would give a performance gain, will try without.

Appreciate looking into it - I'll be very happy if I can get to 3 seconds, happy days!

PC spec
CPU: Intel i5-7400 @3GHz
Ram: 8Gb
OS Win 10 64bit
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Hey @GraH got it down to 3 seconds, is much faster now, thank you very much!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,252
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top