Power Query returning results very slowly

[JackDanIce]

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

 

[GraH]

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.

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]

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]

Hey @GraH got it down to 3 seconds, is much faster now, thank you very much!

 

[GraH]

Great to hear.