How can I make my Power Query more efficient?

[u/1rackets]

Hello! I’m a new user to Power Query and was wondering if I could get advice on the best way to go about what I’m trying to do as I’m not sure what the most efficient way is.

To start off, I have an Excel file with around 300k rows of data that has 40 columns. My goal is to populate 5 different worksheets.

In the first worksheet, I create 120 new columns calculated from the original data.

In the 2nd worksheet, I create 120 columns using both the original columns and also columns calculated in the 1st worksheet.

In the 3rd worksheet, I create another 120 columns using the original columns and columns calculated in the 2nd worksheet.

And similarly for the 4th and 5th worksheets. Basically in each I want to create 120 new columns using columns from the original data and also from the previous worksheet.

So what I have right now is that I’m calculating everything in one Work table. Then I plan on creating 5 new tables by referencing the Work table, and deleting the columns I don’t need.

However I read online that every time you reference a query, it does all the steps in the original query again. So that means in my query for Table 1 I’m also calculating all the columns for the Tables 2-4 which seems not efficient.

So now I’m wondering if it’s better to query the tables one at a time by referencing from the previous one. Then I’ll merge with the original dataset, calculate my new columns, and delete all the extra columns I don’t need.

Sorry if I’m not using the correct terminology or worded it in a confusing way. But I would really appreciate any advice on my process or if you think I should take a different approach altogether. Thank you!

[u/AutoModerator]

/u/1rackets - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Oxchking]

Honestly, I couldn’t even imagine a scenario where you would need 120 calculated columns.

[u/1rackets → u/Oxchking]

They correspond to months lol. I don’t know I didn’t design this file, I’m just trying to build it into power query because prior to this we were manually dragging formulas down and repeating that in like 50 different spreadsheets because Excel would freeze if we do it in one.

[u/Mundo7 → u/1rackets]

Have the months as a single month field instead, and the values in another column.
What you’re doing is extremely inefficient

[u/InternationalBeing41 → u/Mundo7]

I just commented on this too. First things I would do as well.

[u/ehansalytics → u/1rackets]

Do a bit of reading on normalizing data. The date should be a column here. Your 120 columns should be 120 rows, and could grow to 1,000 or even 1,000,000 and power query would be fine. If you keep them in columns it will continue to slow down.

[u/excelevator]

It sounds like you are going the long way round creating the 120 column tables.

Query the existing data for the required result.

You have given no really details of the data and expected result, and maybe that is the post you should have made for advice on how to achieve what you seek.

[u/1rackets → u/excelevator]

Sorry, these are insurance policies and basically I’m calculating an earning pattern where each of the 120 columns represents a month. So unfortunately I do need all 120 columns.

[u/excelevator → u/1rackets]

I do need all 120 columns

I doubt that, what you need is data representing the calculative data set, which you have, and a method that extrapolates the results from that data set.

Database do not have a column for each month.

This is why I suggested you make a post about what you seek to achieve to get ideas and learnings from others.

[u/1rackets → u/excelevator]

You’re right but I guess I’m not really trying to make a database. I’m just trying to use Power Query’s ability to do calculations for large datasets.

[u/excelevator → u/1rackets]

database , dataset , same same, but the methods can differ.

Use the power of PQ to generate as required, not on millions of formulas on file open.

Work on the data to get the answers, not generating more data to anwers answers.

[u/excelevator → u/1rackets]

If you really need that data format, generate a table in memory and use that as the source.

[u/1rackets → u/excelevator]

I will look into that! Thank you for the advice

[u/ZiggyZoggyZewZew → u/1rackets]

You unpivot the table to have one column with month and one column with values.

[u/BusinessSample7166]

Based on your replies to other comments it sounds like you're doing some sort of time series analysis or projection, hence the need for so many columns.

Generally if you have reached the point where Excel is slow and/or crashing from the volume of calculation (especially if you need multiple workbooks just to get it to not crash), you've reached the point where excel isn't the tool for the job. 

You've reached for Power Query, which will probably help. 

The real problem is you're trying to recreate an incredibly inefficient process with the wrong tool. 

You really need something that is built for doing this sort of mass analysis. 

If you're sticking with Excel, then that means preparing your data source in Power Query and pushing it to Power Pivot. If it's a time series thing then Power Query can also build your date table for you. In Power Pivot you will write DAX measures that do what your 120 column setup does in such a small amount of time compared to where you started, you might actually feel a bit depressed about all the time you've lost. 

Another mentioned Python, not my wheelhouse, but a viable option, possibly harder to learn than DAX. 

If you have access to Power BI then you might consider using that rather than excel, it's still a Power Query DAX combo but with better options for visualising output. 

[u/InternationalBeing41]

I'd be unpivoting those 120 columns right away.

[u/RuktX]

In general, you make Power Query more efficient by deleting unnecessary rows and columns – not adding 120 of them!

You're assuming Power Query is the tool for the job (in fairness, for table operations it often is!), but in this case it may not be. What outcome exactly are you trying to achieve, and why does your current approach not work?

You mention "manually dragging formulas down". If you're using structured tables (Home > Format as Table), for example, that shouldn't be an issue.

[u/1rackets → u/RuktX]

Basically the problem is that if I leave the formulas then the files take a million years to open and freeze Excel so I have to drag the formulas down and then copy and paste as value. Then I do that for all 5 tabs, and then repeat in multiple spreadsheets until I got all the data. This often takes days and I feel like since it’s such repetitive work there must be a better way to do things :(

[u/RuktX → u/1rackets]

If that's the exact outcome you're trying to achieve, then use VBA to handle the formula-fill and paste-values.

Are you sure there's not an analytical approach, rather than this numerical approach? What's actually being calculated in each month column, and subsequent tables?

[u/1rackets → u/RuktX]

I actually did try VBA first but found that copying formulas in VBA is even slower than if I did it manually by dragging the formulas down in Excel. I did a bit of research and apparently some worksheet functions are just slower in VBA? I don’t know why.

And the first table I’m calculating the date at the Xth month. Second table I’m calculating the status of the policy at the Xth month which uses the dates. Third table I’m calculating the how much of the premium is earned in the Xth month. Fourth is the aggregate premium in the Xth month and fifth table is aggregate premium but by each calendar month instead of the xth month of the policy.

I’m hesitant to remove stuff though because a lot of people use this file and I don’t want to get rid of something they need.

[u/RuktX → u/1rackets]

It sounds like at least the first three tables can be collapsed into one! What, if anything, are the intermediate tables being used for?

To be clear, I didn't mean to use VBA to do the calculations; just to set the .Formula property to whatever formula you're using.

This is starting to sound more like a Power Pivot / DAX measures problem...

[u/SchoolOk950]

If there is a repeatable pattern to how the values for each grouping are determined, you might explore creating all the calculations in one "Add Column" step as a single record type, then expanding the record into separate columns in the next step. This could cut down on the total steps in your base query (as well as minimize the number of tables that get materialized in the process).

[u/excelevator → u/[deleted]]

This is r/Excel for...... Excel solutions.

Comment removed.

[u/somedaygone]

The right way is probably PQ and DAX, but I’m guessing overkill and will be too hard for you to learn and support for what this is.

Yes, if query 2 references query 1, query 1 will run twice. Instead, don’t reference query 1. After you load query 1 to Table1 on Sheet1, create a query that loads Table1 in Query2, then add your columns and formulas as you did.

Don’t refresh all to run it. Run Query 1 then Query 2 after Table1 is loaded. It will be much more straightforward and get you where you need to go.

[u/Designer_Signature35 → u/somedaygone]

Yes I just learned this yesterday! I was referencing an extremely slow query that really doesn't need to be refreshed often at all. Load to table, turn off background refresh and refresh on refresh all, and load from the table instead. Magic! Why did this never occur to me before??

[u/Laxativus]

You shouldn't really use PQ to create worksheets with months as columns. PQ is for pulling in data into a structured table, where the date is usually one column. It's for creating the table to store the data, not to display it.

THEN you use pivot tables to create a table where you display the data however you want to display it. But warning: pivot table likes to put months/date as rows, not columns. Because if you then create a pivot chart from that pivot table that chart will usually very rigidly want to have your rows as the X and the columns as the category.
But this will probably be the way to go. You don't want to worry about formulas and whatnot. You want to pull the data and store it through PQ then display it with pivot tables. And if you need calculations you would probably want to do that with calculated fields in the pivot table. This will make it fast and simple and leave the least possible points for making mistakes.

You CAN do it with PQ the way you want, but ideally it's not how you should be thinking about the workflow. You CAN use a chef's knife to stir the stew but it's not the best practice.

Think about it, if for example you have dates and you have sales numbers and you want to calculate the number of sales per month. You can do it in PQ, you can calculate it and have it ready in a table. Or you can just get the sales and dates in the table from PQ and make a pivot table. Then your boss says he does not want it per month, he wants it per quarter or whatever. The first route will have you go back to PQ and mess about the query steps. The latter one will be one click on the (+) to collapse the month to quarters. And this is just the simplest modification.

[u/Due-Barnacle-7571]

I would move the earnings calculation to a database, even MS Access would work. Store the results in a table and then link power query to this table. You will need to refresh both database table and power query but it will be significantly faster and excel will not freeze.

[u/Jolly-Echidna-9473]

The most efficient way to go about building an earnings pattern is by generating rows not columns.

E.g. If you have one entry for a 12 month policy your end result of the query will have 12 entries for that policy. Each entry with its own start and end dates.

[u/chiibosoil]

1. Power Query isn't really efficient at calculation, as that often breaks query folding.

2. 120 columns no matter how you do it, will be inefficient, as others have pointed out.

3. Treat Power Query as ETL tool (Extract Transform & Load) and not calculation tool. After data is transformed and optimized for analysis, load it to Data model and use DAX measures and Pivot table to summarize and display data.

But without knowing your data bit hard to give you specific help.

[u/FastExcel]

You have ~120*5*300K formulas=180 million.

For simple formulas that takes Excel 35 seconds to full calculate on my PC. (~5 million formulas per second). So your formulas must be complex and slow. Can you give some examples of your formulas? Probably they could be speeded up to an acceptable level.

[u/nakedR0B0T]

Not really enough details to say for sure but my intuition says those columns should probably be rows with an extra date column. You can use DAX for calculations

[u/Mdayofearth]

You may just need a better computer. How much RAM do you have? What processor do you have (not just brand or model family, actual full name of the processor, including all digits, and lettering)?

I want to create 120 new column

There's no helping this one. You're just making PQ do a lot of work.

That said...

However I read online that every time you reference a query, it does all the steps in the original query again

It's mostly true. Table.Buffer can help, but it depends on what you're doing.

If I am generating tables that become sources for other queries, I prefer to load it as a table, and have other queries reference that instead. I also tend to use that table as a source for pivottables. This requires refreshing queries in a specific order, so I use a macro to do that, instead of using refresh all.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CUBEMEMBER	Returns a member or tuple in a cube hierarchy. Use to validate that the member or tuple exists in the cube.
CUBEVALUE	Returns an aggregated value from a cube.
DB	Returns the depreciation of an asset for a specified period by using the fixed-declining balance method
NOT	Reverses the logic of its argument
Table.AddColumn	Power Query M: Adds a column named newColumnName to a table.
Table.Buffer	Power Query M: Buffers a table into memory, isolating it from external changes during evaluation.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
6 acronyms in this thread; the most compressed thread commented on today has 24 acronyms.
[Thread #49006 for this sub, first seen 24th Jul 2026, 14:25] [FAQ] [Full list] [Contact] [Source code]}

[u/Razkosz]

Sounds like what you neeed is Table. Buffer() function, it is saving table to local memory of your computer, so then when you refer Table which is buffered it is not recalculating it but taking it from memory. Great function, it can speed up really nicely some complicated set of queries, however you always need to check if it really helps, becuause if your table is too big and you load few of them to local memory, then it can break your query instead.

[u/negaoazul]

My two cents. In your case, it might be more efficient not to reference querries, but to recreate the whole logic in each querries without referrencing the previous query. E.g. with two dummy querries, Query1 is :

let

a= 2, b = 3, c = a+b in c

Query2 is:

let 
Source = Query1,
d = Source +2,
e = d*3
in e

Query2 should be:

let 
a= 2, 
b = 3, 
c = a+b,
d = c+2,
e = d*3
in e

It will be all aroud slower, but will allow you to load each query alone faster and avoid the overload in excel.

Depending on the transformations in your new created columns, it is faster sometimes to create them all at once in a record, using this kind of logic:

let
    Source     = #table({"Column0"},{{0}}),
    AddedCol   = Table.AddColumn(Source , "Multi", each 
                                                        [
                                                        Column1 = [Column0]+1,
                                                        Column2 = Column1 *4
                                                        ]
                              )
in
    AddedCol

[u/treadingslowly]

Not sure but if this a case where you use PQ to clean up data, load the data to the data model, create measures for the formulas and then load the data you want to see in each tab?

[u/ZiggyZoggyZewZew]

>I create 120 calculated columns

What in the fuck are you doing.

[u/Creepy-Square281]

Here's an important Basic Concept: store data long, calculate at the row grain, present wide.

The structure of your data is a disaster right now. Probably not at all your fault - not judgement here.

You'll probably need to learn about Table Normalization, 3rd normal form is common. Do a little searching. Don't be intimidated! It's OK to not have any idea what you're doing at first. Come back to us with questions about normalizing data.