•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Power Pivot and Data Models - updating data? Switching out what's behind pivot?
Our take
Power Pivot and Data Models offer powerful tools for managing larger datasets and creating dynamic pivot tables. If you've updated your dataset but find that simply replacing it disrupts your pivot table, consider using the "Data Model" features effectively. Instead of deleting or renaming queries, explore how to refresh the connections within the data model. This allows you to maintain your calculated fields and measures while ensuring your pivot table reflects the latest data.
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?
[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]
- How can I create a pivot table with the PowerPivot Measure being used for the rows?Building an inventory valuation model. Basically, taking data from sales, warehouse inventory, and stores to find lower of costs/sales value. I'm also applying a obsolescence formula that basically just says "if we sold 12.5% of our all available units over the year, then mark it as current, otherwise mark it as the last date we received it in the warehouse". This measure is named "Slow moving date". I'd like to use "slow moving date" as a row in a pivot table, but Excel says that value can't be a row. How can I force it to allow the dates as a row without having to copy/paste numbers only from a different pivot to make it work? submitted by /u/scaredycat_z [link] [comments]
- Excel Power Query: Renaming Queries Already Associated with Pivot Tables & ChartsHello! I created a massive workbook for a client and parameterized the geography and vintage of my queries in the Power Query Editor so that I can easily make a similar file with different parameters. The problem is I named the queries themselves too specifically (see pic) and now I want to change them but it breaks all of my associated pivot tables and charts. For example, I would like to change ACS_Poverty_County_1YR to ACS_Poverty so that the query names are not misleading when I create an MSA/Zip Code version of the file. This file is way too big to remake so I'm desperate to find a way to fix it without having to remake the visualizations. Any help is much appreciated! https://preview.redd.it/eszqgftt8nsg1.png?width=490&format=png&auto=webp&s=dcaf3b4317b4e955da792bdefbc4ead98ad96f42 submitted by /u/classicjam [link] [comments]
- PowerQuery and add manual dataHi everyone, I have a Power Query in Excel that outputs a table with [title] [date]. I need to manually add the Sprint number in an extra column [sprint] to specific combinations, aka "I will work on this this month." The problem is every time the query refreshes, any manually entered data gets lost or misaligned. - New rows come with the needed values. - Row order changes. Because this table is used by many people, I want them only to add the sprint number, nothing else, no copying data or anything. I would like to know more about your experiences when data needs to be written infrequently but many times. I am open to know more for powerbi options direct dashboard too. submitted by /u/No_Solid2349 [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#real-time data collaboration#big data management in spreadsheets#conversational data analysis#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#Excel compatibility#financial modeling with spreadsheets#Excel alternatives#rows.com#natural language processing for spreadsheets#large dataset processing#real-time collaboration#Power Pivot#Data Models