•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Does power pivot refresh in sync with power query
Our take
If you're navigating the complexities of Power Query and Power Pivot, you're not alone. Many users seek to understand how these tools interact, especially when creating pivot tables from transformed data. Your concern about pivot tables refreshing before Power Query outputs is valid, but utilizing Power Pivot can indeed streamline this process. Unlike traditional methods, Power Pivot is designed to efficiently handle larger datasets, minimizing memory strain on your system. This approach may provide a smoother experience while summarizing your results effectively.
I have a file that had an extensive power query transformation
And I essentially need to make a few different pivot tables using the PQ output to summarize some results
But I’m concerned that if I do that the pivot table will refresh before the power query output refreshes
If I use power pivot (which im unfamiliar with) Will this issue be avoided
I could just use power query to get the results I need in the desired format but my system is 32 bit and that would murder the memory since I’d have to run the same query 3-4 times and as I understand it power pivot is much easier on the memory for visualizations
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Power Query + Power Pivot + DAX = fast and powerfulI inherited a monthly Update that is mostly the same data updated and then some analysis. I paste the raw data from 9 different places, manually aligning them as a single table. There are a load of pivots that have calculated fields and items and updating them take a morning and most of that was just waiting for the calculated items in the pivots to refresh. While I was waiting for that to refresh, I decided to see if I could recreate the same pivots in a new sheet, using powerquery. In the time it took for my original sheet to refresh, I loaded the data into powerquery, aligned the data, loaded everything into the model, created custom lists for the columns to look up off to allow for differences in the names between data sources, created extra lookups to improve the filtering, created the relationships, created DAX functions to replace the in-pivot calculated items and fields and recreated the tables and charts I use every month. The original refresh finshed about 5 minutes after I did all that. I did a test refresh and what took a morning now takes about 3 minutes on my new sheet. I'm a fairly recent convert to PQE, but this little project is the first time I've really dug into Power Pivot. It could be a game changer for a lot of my work. There's stuff I was doing in PQE that is a lot easier to do in PowerPivot. Being able to link lookup tables, data tables and facts together is really fun. submitted by /u/Thisoneissfwihope [link] [comments]
- Power query and manual table next to itHi, I want to pull data verbatim from a spreadsheet my team uses and use data from it for my own purposes. The main goal for using power query is that the data updates on my spreadsheet. Mainly, if any new entries are added at the bottom. I also have some manual fields that I need to add that correspond with the power query data. I've added another table beside the power query data, and filtering it causes the data on both sides to adjust correctly. I'm mainly concerned that, if the entries are rearranged or sorted on the original sheet, that my tables will not align after a refresh. Also, if a refresh would break my table alignments at any point. Is my fear founded? Is there a way to combine the two features that I need into a single table? submitted by /u/Perspective-Guilty [link] [comments]
- Power Pivot and Data Models - updating data? Switching out what's behind pivot?I'm trying to learn Power Pivot so I can use larger and different data sets using data models. For now, I only care about a single large data set (though I ultimately have others too) and a corresponding pivot table. The data set is in the data model as "load to" connection only and add to data model. I re-ran that data set updated and want to do what effectively I do with Excel datasets, just copy paste over the first and refresh the pivot table. I can't delete the original query and rename the new one and repoint because the pivot table automatically is cleared. I don't know if there's any other way to remove the original data set and overwrite with the new? I have one measure or calculated field in the data that I did recreate. I want to avoid recreating the pivot every time because that's inefficient. How do I approach this? submitted by /u/JayReddt [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]
Tagged with
#Excel alternatives for data analysis#rows.com#financial modeling with spreadsheets#natural language processing for spreadsheets#generative AI for data analysis#digital transformation in spreadsheet software#Excel compatibility#Excel alternatives#power pivot#power query#pivot tables#refresh#transformation#memory#visualizations#32 bit#output#results#concerned#extensive