Updating formulae using Power Query

[tiredofit]

Further to my initial post here:

https://www.mrexcel.com/board/threads/updating-formulae-fast.1239419/page-2#post-6073100

can someone tell me if Power Query can help?

To summarise, I have a workbook with a worksheet containing many columns.

The former columns are constants, the latter columns refer to the former columns, as well as to other worksheets within the workbook.

Updating the formulae by selecting a row, clicking fill and dragging to row 100 is fairly fast.

However, dragging to row 1000 is very slow.

Can Power Query help with this kind of problem or must the design of the workbook and / or worksheet be changed first?

Thanks

 

[GraH]

First thought is: " Yes, PQ can help. " As always it's more about context. F.e. how complex are those fxs? What functions are used? Do they only work on row level of not?
A sample set is useful here.

 

[tiredofit]

First thought is: " Yes, PQ can help. " As always it's more about context. F.e. how complex are those fxs? What functions are used? Do they only work on row level of not?
A sample set is useful here.

The formulae are:

SUMIFS, MATCH, VLOOKUPS

and some refer to other worksheets within the same workbook.

 

[GraH]

Vlookups you can do with PQ (merge queries), sumifs is likely to be a group by. Match is a kind of lookup, so probably merge again.

I would keep the "raw data" and the lookup tables externally to my result workbook.
Depending on the case, do some merges and group by in PQ.
Alternatively maybe build a data model to link the working data with my lookup tables. Does not need any vlookup kind of thing. This way I can do data crunching with (power) pivots.

Inside Excel, on the data ribbon there is something call "relationships".

Inside the data model it looks like this, simply select the field to link from the data table to the lookup table.

 

[tiredofit]

Vlookups you can do with PQ (merge queries), sumifs is likely to be a group by. Match is a kind of lookup, so probably merge again.

I would keep the "raw data" and the lookup tables externally to my result workbook.
Depending on the case, do some merges and group by in PQ.
Alternatively maybe build a data model to link the working data with my lookup tables. Does not need any vlookup kind of thing. This way I can do data crunching with (power) pivots.

Inside Excel, on the data ribbon there is something call "relationships".

View attachment 93762

Inside the data model it looks like this, simply select the field to link from the data table to the lookup table.

View attachment 93761

Thanks for your detailed explanation.

I will try it and see how far I get.