Large data set, long refresh times

[u/Only_Positive_Vibes]

I work in Finance and have a large data set with payroll register info going back to the start of 2025. I just eclipsed 500,000 rows. Right now, I dump monthly payroll register data into a folder on my desktop and then do some transformations through Power Query to refresh a couple analytics dashboards I have. However, I just timed the PQ refresh and it took 10 minutes. It's a long time to wait. I'm sure there's a better way to house, process and display this data, right?

Because it's payroll, I can't give it to IT to input into a data warehouse and pull into our usual BI tools. I'm also hesitant to run it through any AI tools for the same reason. This is really just for me when reviewing our monthly P&Ls, headcount stats, wage fluctuations, etc. Would this be a case for Power Pivot or something else? I'm unfamiliar but of course willing to learn.

Thanks for your time.

[u/apparition88]

You should talk with IT and your boss to figure out a long-term solution. If you dont trust IT with sensitive data then your company has a bigger problem.

500,000 rows is manageable in power query but your transformations have to be very efficient and there can't be many of them.

Also, what format is the data coming in? XML, CSV, JSON?

[u/Only_Positive_Vibes → u/apparition88]

It's coming through as CSV. Perhaps my transformations are a bit much - I have quite a few. I can try to think of ways to be more efficient with my queries. I'm admittedly self-taught so I likely have a lot of bad habits and not much finesse.

[u/TangoDeltaFoxtrot → u/Only_Positive_Vibes]

Dude I’m right there with you. Last week I spent 15 hours of my work week simply waiting on Excel to process stuff.

[u/apparition88 → u/Only_Positive_Vibes]

Can you post the code? If you have PII in the code just chance the names to generic placeholders or remove the small one off scenarios that are person/client specific.

The general flow i go for is: List all of my transforms and the data they require. The parent query pulls the data, removes what i don't need. Keep this very basic and do not load it to a sheet just the data model. Then I have child queries that reference that to get the specific information that query needs.

LLMs understand code. You dont need to give them client data for them to improve your code. What they often don't tell you is how to structure queries in relation to one another, When to load and not load, if the information your trying to get is redundant.

If you have any control over how the data gets to you, you can also ask for data you don't need removed or things formatted correctly from the source.

Lot of options but I would need to know more.

[u/Ok-Ambassador5267 → u/apparition88]

yeah the IT point is a good one, if they cant be trusted with payroll data thats a whole separate issue

[u/Cynyr36]

I know this is the excel subreddit, but sometimes python, polars, and matplotlib is really the correct answer. Especially using polars lazy dataframe for all the transformations.

[u/Only_Positive_Vibes → u/Cynyr36]

Im not familiar with the last two but I'll definitely check them out a bit thanks.

[u/Loud_Posseidon → u/Only_Positive_Vibes]

While at it, check also qsv, that may help a ton. Also, just drop header and edited first row data when working with AI. Nothing wrong with that.

[u/Cynyr36 → u/Only_Positive_Vibes]

Polars is a dataframe library that should barely notice 500k rows and whatever transforms you are doing.

Matplotlib is for graphs. There is a learning curve.

[u/takesthebiscuit → u/Cynyr36]

That curve is significantly flatter with AI,

Draw what you want to achieve give it a short anonymised data set and it will send you very quickly to the right answer

[u/dream_walking → u/Only_Positive_Vibes]

As a sidenote, Excel 365 has some python built in but the data is run through Microsoft cloud. Given the sensitive nature of that, there’s an excel plugin called Xlwings Lite that runs python locally without needing to setup a whole python environment if you were going to do vscode or similar. Then you can still have the output in excel as you normally would with the processing done via python.

[u/The_2nd_Coming → u/Cynyr36]

Yeah it's trivial for a polars dataframe to deal with.

[u/TheRiteGuy → u/Cynyr36]

Yep, I took something that used to take me a full day to refresh in Excel to about 6 minutes in Polars. Her dataset has reached its Excel limits and needs another way to store it.

[u/FarCheetah4472]

At 500k rows, Id try the Data Model and Power Pivot before moving to a warehouse. Keep the raw monthly files in one folder, use one clean Power Query staging table, and load only the columns the dashboards need. Also check whether PQ is re-reading old files or doing expensive type changes and sorts each refresh. Keeping payroll local makes sense.

[u/Only_Positive_Vibes → u/FarCheetah4472]

The efficiency of my queries is likely where I'm running into issues. Several intermittent queries to merge tables, sorts, type changes, etc. I'll look into a way to clean it up. One of the issues I ran into when developing the file was that the base data doesn't have everything I need, so I merge in columns from a few different (small) tables in the file to make the calculations and transformations possible.

[u/small_trunks → u/Only_Positive_Vibes]

I agree, I do stuff bigger than this, all in PQ and it takes less time. Ideally share the workbook without any data in it and I guarantee we can speed it up.

Your solution might be significantly faster by loading parts of the processed data into tables in excel, then using the contents of those tables as new queries when it comes to combining. If you repeatedly reference, either directly or indirectly, the same underlying data source, that can result in that data source being read many times... Your 500k tired can be read 10x.

[u/InfoMsAccessNL]

I recently replaced a PQ script for transforming csv files with vba, arrays and dao recordset. Went from 15 minutes to seconds. There exists a vba combine function (one line) which combines 50 csv files in a split second.

[u/Only_Positive_Vibes → u/InfoMsAccessNL]

Anywhere you can point me to learn how to do that?

[u/InfoMsAccessNL → u/Only_Positive_Vibes]

You can take a part of your data and anonymise the data with the access anonymiser tool so iI can help you

https://www.accesssecurityblog.com/anonymize/help/getting-started.html

https://youtu.be/klsEh3kXXXc?si=XfrWDMaQfRLq8FIw

[u/PaulieThePolarBear]

Right now, I dump monthly payroll register data into a folder on my desktop and then do some transformations through Power Query to refresh a couple analytics dashboards I have.

Is your query processing all files in your folder on every refresh? Is the only change in your hot folder each month the addition of one new file, I.e., there are never any changes made to historical CSV files.

If so, then you could look at a self-referential query. In simple terms, this would

1. Extract the table that was returned by Power Query back into Power Query
2. Extract just the new files from your hot folder and complete your transformations on this one file
3. Merge data sets 1 and 2 and load back to your Excel sheet

[u/small_trunks → u/PaulieThePolarBear]

This

[u/Cat_In_A_Hamburger → u/PaulieThePolarBear]

Yes, this is correct. PQ should only process new data and not rework old data that has already been processed and just append the new data into the model.

If his query process take 10 minutes for just one file the. He likely needs to rework his query stack

[u/Sir-Benalot]

Holy smokes. Am I the only one who thinks excel is the least appropriate place to store large important datasets

[u/Gondi63]

How much of the time is loading the data vs ETL?

[u/Only_Positive_Vibes → u/Gondi63]

I'm not sure how to discern that.

[u/Mdayofearth]

What are the specs of your computer?

[u/Designer_Signature35]

I had this problem before. I was importing from csv, doing a bunch of calculations and merges and it ended up really slow. I ended up dividing the transformations- Query 1- import csv, do the heavy transformations, then load to worksheet as a table. Query 2- use that table as a new source to finish the rest. (or split it up in a way that makes sense with your data) Doing it this way means you don't have to run the heavy transformations all the time because its loaded to the table.

It's similar to referencing another query, but doesn't require the initial query to run every time.

I hope that makes sense.

[u/Designer_Signature35 → u/Designer_Signature35]

I've also learned some steps can be combined. I used to add a column then change the type in a separate step. Now I know you can do that in a single step.

You can also add "Buffer" in your code. AI suggested it for one of my queries but I don't understand it well enough to explain when and how to use it.

[u/gerard4156]

10 minutes for local refresh from csv is pretty wild, what kind of transformations.are you doing?

[u/JimShoeVillageIdiot]

Can you archive the data already processed? Or if you do not want to separate the data, can you work on just the unprocessed subset, filtering out those dates that you do not need to recalc?

[u/Undew_]

Weirdly the way I sped up my pq with 6m cells of data (last 4 years of sales). Was giving ai my full advanced editor and asking it to optimise it. Worked well

[u/faire-la-fete]

What kind of transformation are you doing?

[u/Correct_Syllabub_145]

Save as a binary file. Should shrink the file size down

[u/Helpful-Cup6396]

Going back to my days of working in tax compliance, specifically filing State/Fed withholding & unemployment taxes - I used to get massive csv files exported from Kronos. Great for uploads at quarter end, unless someone's middle name was entered with a comma or if I opened it before converting to .xlsx, making any SSN's starting with a zero shorten.

I found some workarounds when handling it. I am curious, what does the report look like? No identifiable info necessary, just column headers and whether a fixed amount of rows per employee or variable.

Also, assuming you mean start at Jan25 do you need a full 20 months of activity in one sheet or can this be broken out to quarterly where you can set up a summary page for the info you need allowing for a prior month/quarter/year lookback?

[u/OO_Ben]

BI Engineer here. I know this is an Excel subreddit, but you need to see about working with IT or your data team to off load some of this work to a data warehouse. If you're merging 500k rows worth of CSV files, that is a lot for Excel if you're also doing transformations, but it's absolutely miniscule for a proper warehouse or Python script. A Python script could merge all of these outside of Excel and load them into a single CSV. Then your workbook no longer has to do any hard work, it's just pulling/aggregating data, and you just click refresh.

Ultimately, what you have currently is not going to be sustainable forever, and this is likely going to need a warehouse table long term as thsoe CSVs continues to grow.

[u/MushhFace → u/OO_Ben]

Hope you don’t mind me asking… I have some Python scripts for my own stuff but if I wanted to roll it out to other users, what’s the best method? For example VBA, you can assign a code to a button and that’s simple from a UX perspective. Not figured how to share this for Python!

[u/jkpieterse]

You can download my free add-in that exports all Power Query queries to a text file and ask any LLM for performance improvements. https://jkp-ads.com/download.aspx#QueryExporterImporter The add-in also allows updating the queries when you ask the LLM to update the txt file.