•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How Do I Speed Up My PowerQuery
Our take
If you're struggling with slow performance in Power Query, especially when working with complex custom columns, you're not alone. Many users face similar challenges when converting raw data into actionable insights. Despite your efforts, such as creating a library for logic and branching queries to reduce data load, it can be frustrating when data fails to load effectively. Explore strategies to optimize your queries further, streamline your processes, and enhance performance without compromising the integrity of your data transformations.
My powerquery is so slow even though I tried to make it faster. The data In working with is just the raw data and I’m tasked with converting everything into something else through custom columns.
These custom columns use legends by merges and are nested if statements. The excel equivalent would be vlookups with nested ifs.
I made a library inside Powerquery that houses that logic.
I also branched the query into 2, one where the columns aren’t used in the custom column creation and another where it is. I know this decrease the amount of data since the columns went from 20s to 7-8.
The issue is the data doesn’t load. And I didn’t even remerge the constant with the custom columns throigh an index column, then append everything.
I tried Buffering the alleged which didn’t do a thing
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Power query for a large datasetMy company uses a horrible format for its daily production sheets, but the data can be pulled through power query. I want to build a reporting tool for looking at any major trends that are currently missed. Ideally looking at part efficiency by machine type and some other descriptive data too like efficiency by shift manger etc. My problem is that even after cutting unnecessary columns and filtering unnecessary rows, it takes forever to load anything. ChatGPT isn’t all that helpful, I’d like some expert advice please! For info, rough number of rows of data is about 50,000 per year. I want to cover at least the last three years. Sheets are all saved into a folder by month, within a folder by year. submitted by /u/CanJesusSwimOnLand [link] [comments]
- Power query multiple workbooks and sheetsI am trying to combine multiple workbooks that have multiple sheets but I am missing a step. Get data, from file, from folder and open. Transform. Add custom column (=Excel.Workbook([Content]). I select data, item and kind from the custom drop down. Then I filter item to select the specific sheets I want. Close and load. When I do this, I only get the filepaths of the sheets I selected. I tried the same steps, but I clicked on the "Table" link in the custom data column before close and load. It brings up the data for that worksheet and it's correct. But when I then close and load, it will bring up the actual data, but only for that worksheet that I looked at. How do I get the *data* from all the worksheets that I selected in custom data to load. Or am I completely off base? Thanks! This is my first foray into power query and it's a bit daunting. submitted by /u/Choice_Intention_778 [link] [comments]
- PowerQuery hangs forever in refresh when multiple computers are used to editI'm having a sudden problem in which when I make edits on another computer and try to refresh PowerQuery on my primary computer, it refreshes forever or sometimes refreshes for hours before throwing up a random memory error. Normally, the refreshes take between 3 and 10 minutes. I've used this setup for a year now with no issues before last week. I have an relatively elaborate PowerQuery setup that pulls in shared data from many sources, including online, Dropbox, SharePoint, OneDrive, and in-workbook excel tables. Lots of merges, lots of custom formulas. All file locations are variable parameters that are changed via a dropdown in Excel. I do most of my work on an up-to-date Windows 11 version of Excel running on a Mac via Parallels. The other machine I occasionally edit on is another up-to-date version of Excel on Windows 11. They are synced via Dropbox. At first I thought my VM was corrupted, so I reinstalled fresh. It worked fine until the next time I edited on another machine. I've deleted all PowerQuery and Excel caches and it still happens. Nothing I'm doing is new, so I'm not sure why it suddenly breaks. Any thoughts? submitted by /u/WorldsGreatestWorst [link] [comments]
- How do I Maximize File EfficiencyI work with data sets that I typically look at forecast by year. Currently when I look at 2026 and 2027 it is rougly 1.4M lines of data. I have to put these in two separate data pulls and tables. Then I have six different customers included in this data. so I have to create 6 tabs with six diffrent pivot tables for them to look at. This has created a massive file that lags just to open, save or close so I really have two questions and am open to suggestions. Would it be better to store the data in one worksheet and then link a second worksheet that just has the pivot tables and separated look? If so how would I creat that link? Can you explain to me like I am 5 how I would use power query to combine the 26 and 27 table so that they could be in the same pivot table? Every column in both are identical. submitted by /u/dcal69 [link] [comments]
Tagged with
#Excel alternatives for data analysis#big data management in spreadsheets#generative AI for data analysis#conversational data analysis#real-time data collaboration#financial modeling with spreadsheets#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#Excel compatibility#Excel alternatives#rows.com#PowerQuery#custom columns#nested if statements#raw data#merges