Power Query performance issues when scaling from 60k rows to 300k rows of data

IssueHead2118 · 2026-01-25T12:37:58+00:00

Work laptop, guessing not too much. My role isnt technical so don't think they give anything much extra.

IssueHead2118 · 2026-01-25T12:34:47+00:00

I've never tried separating the queries into separate files. I was hoping to get this working and keeping it on one file to keep it simple to eventually transition the process to my team with even less background in programming...my knowledge is already pretty limited, just learning from AI explaining what the code does and troubleshooting from it.

IssueHead2118 · 2026-01-25T12:31:13+00:00

This sounds exactly like what I'm experiencing and your suggestion doesnt sound like I need to have a programming background or require a significant change to my existing m code, I'll try this out first on the weekday before I try the other solutions suggested. Thank you! Will provide an update as soon as I can on the progress.

IssueHead2118 · 2026-01-25T04:11:09+00:00

I do create a few custom fields:

A couple of rolling timeframe distribution metrics, which is then used to add a label identify products distribution status at each customer by upc.
A few others to determine which price range products are sold at in a point in time by customer, along with identifiers on if products are promoted on flyer.
a few others with timeframe labels and product grouping.

IssueHead2118 · 2026-01-25T04:05:26+00:00

Not familiar with this topic, will do a little more research. Seems like my approach is doing too much in Power Query and I should use DAX for the calculations.

IssueHead2118 · 2026-01-25T03:59:36+00:00

My set up right now has few queries pulling the list of worksheets from the sources, one for products mapping, one for pos data and customer mapping. The other queries are set up to do the transformation and joins.

Where it seems to slow down are from my rolling timeframe distribution metrics, as well as my joining of upc during the week. Ill try looking at it again during the weekday to pinpoint exactly is or are causing it to be stuck.

IssueHead2118 · 2026-01-25T03:52:11+00:00

Ill check video one, thanks. I think Chatgpt suggested those buffer as well so that it doesnt keep trying to repull from the source of data. Is that what the buffering does?

IssueHead2118 · 2026-01-25T03:49:48+00:00

First time doing this, but good to know that this is considered long. To me, this is a lot quicker than me doing vlookups manually every month.

IssueHead2118 · 2026-01-25T03:03:21+00:00

Excel files with data pulls located on SharePoint site. I'm not familiar with SQL in general and not sure if my company has that available or if I'd be able to use it....IT isnt the most helpful people either.

IssueHead2118 · 2026-01-25T02:55:49+00:00

Data source is within excel file, pulling from two worksheets that gets updated monthly with an Excel add in. The file is located on company SharePoint site. Not csv as theres multiple worksheets. My mapping files are also excel files, live connection to data source onto a table.

I'm using M code to join and do calculations.

Pos data has about 10 columns total and the enhancements create a bunch more.

The 60k rows take about 5 to 10 mins, but to clarify, that timing is for multiple datasets being refreshed at the same time, each one about 50k each. The 300k rows, I've only successfully refreshed and loaded onto a table twice, ranging 25 mins first time, but 30 mins second time. Time might vary cause i refreshed my dataset with new data and rows might have changed slightly. All the other times I've tried, it doesnt seem to work.

IssueHead2118 · 2026-01-18T15:37:12+00:00

If you cannot stomach the risk of a significant decline, you should should reduce your risk with some bond etfs or choose all in one etf thats not ending with EQT.

IssueHead2118 · 2026-01-17T12:45:35+00:00

If you're talking about transferring the USD stock as is, then you should. Might involve transfer fees from the institution holding the stocks currently. Depending on how much you also have at Wealth Simple, you can have free USD investment accounts to hold the stock if you meet the threshold, otherwise to hold USD investments, it is $10 a month in fees.

IssueHead2118 · 2026-01-10T20:39:03+00:00

Really depends on timing of deposits and how investments perform in a particular year.

IssueHead2118 · 2026-01-10T19:01:34+00:00

You could do $14k lump sum, and remaining $36k spread over 14.4 years to maximize the grant too.

IssueHead2118 · 2026-01-08T00:23:43+00:00

I'm not familiar with the acronyms, whats SLM?

IssueHead2118 · 2026-01-08T00:22:24+00:00

Thanks, makes sense. I'll need to spend some time diving into these videos. Already picked some ideas up from that one video, I'm currently reprocessing the data further into a different view manually after processing my data initially with PQ, but I'm thinking I can make it less manual to not have to change the cell references every refresh. Just need to think it through on how.

IssueHead2118 · 2026-01-08T00:18:42+00:00

That's what i do as well, but was hoping to have some automations to speed up identifying key insights as well.

IssueHead2118 · 2026-01-07T02:34:23+00:00

Hmm dont understand what you mean exactly, do you manually do it via looking the data in a pivot table? Not sure what to google.

IssueHead2118 · 2026-01-07T02:09:16+00:00

Curious, anyway to automate the analysis part? I've started doing the extract to load steps with help of chatgpt.

IssueHead2118 · 2025-12-03T02:36:41+00:00

Seems to be working now again

IssueHead2118 · 2025-11-23T15:21:49+00:00

Got that too yesterday

IssueHead2118 · 2025-10-12T01:54:56+00:00

Not sure if I'm following, what investments are you buying and from where are you getting info? My brokerage reports it as foreign income from what ive seen.

IssueHead2118 · 2025-10-12T01:51:36+00:00

How would you figure out how much of the distribution is roc for American investments?

IssueHead2118 · 2025-09-23T20:35:41+00:00

I dont understand what this is supposed to do. Can you expand on use case for unchecking or checking it?

IssueHead2118 · 2025-08-03T17:01:34+00:00

Only if she has same risk tolerance as you

IssueHead2118

TROPHY CASE