Power Query returning results very slowly

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,680
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
573
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,680
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
 

JackDanIce

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,306
Messages
5,510,507
Members
408,792
Latest member
S_s_s

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top