Register
Log In
Unanswered
Home
Power Query
October 14, 2015
Offline
I created a pivot table with an Offset formula in the range definition to allow for additional data to be added. It's not working properly and I'd like to see that formula again to confirm its accuracy. Is this possible?
Thanks for your help!
See Answer
Dashboards
Power BI
Power Query
April 21, 2015
Offline
Maybe you can provide an example file? There are more things to know why this doesn't work properly. Depends also on where and how you used your Offset formula.
Dashboards
June 25, 2016
Offline
Have you considered using a Table for the data? The range will auto expand when new data is added.No formulas involved.
Then you only need to refresh your PivotTable.
Answers Post
July 16, 2010
Offline
I agree with Sunny. Definintely use Excel Tables for your source data. Here is a tutorial on Excel Tables as they have many benefits.
Mynda
Power Query
October 14, 2015
Offline
I tried using a Table, but each month's data has to overwrite the prior month, which the table wouldn't let me do. I then tried using an Offset function to enter a dynamic range into the initial "create PT" dialog box which didn't work either. However, when I created a named range with the Offset function, then entered that name into the "Create PT" dialog box, it seems to work.
Thanks for the suggestions and the reference to the Table tutorial. I'm weak on structured references and appreciate the info.
Dashboards
Power BI
Power Query
April 21, 2015
Offline
Hm. Overwriting should not be a problem.
You can try first deleting the data that isn't proper anymore and after that putting in the new data (and after that of course refresh your pivot table).
Other way: first make the table for the new month (I think it's something imported?) on another tab and after that try to copy that data to your table (or first delete the data there etc.).
This is normally possible!
July 16, 2010
Offline
I'm with Frans; overwriting shouldn't be a problem.
Another options is to just use a filter on the PivotTable to choose the month's data you're interested in seeing. If you want to see the 'current month' then you can add a column to your source data that classifies the rows into 'current month' and 'prior months' and use this field in your filter with 'current month' selected. That way you don't have to change the filter selection.
Dashboards
June 25, 2016
Offline
Since you need to override your data you should use what Frans suggested i.e. create a new sheet with an empty Table every month.
I use both overriding and deleting data method and did not encounter any problem at all.
It would be best if you could post a sample file for us to see what you have. It is difficult for us to guess the structure of your table.
Most Users Ever Online: 57
Currently Online: azri adrin, Mark Lund, Rudi
24 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Joy Berryhill
Ajay Mukhi
Stephen Motte
Rodney Hoyle
michael pitre
Samantha Burleson
Krishnaa Kurugod
David McSwain
Shameem Nassiree
Alanna Henderson
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
