Power Query returning results very slowly

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,674
Office Version
365
Platform
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
523
Office Version
365, 2016
Platform
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,674
Office Version
365
Platform
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,561
Messages
5,487,575
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top