Best way to overwrite

[karlitob]

Hi all

Appreciate any advice please.

I have a large dataset - a table of about 100,000 records with about 30 data fields.

I have powerpivots set up on that data. However, the I extract data from the same source every month. Not only do I have 10000 new entries each month, but quite a large volume of the previous 100,000 has also been modified by other users. The original source is modified daily.

In order to keep the format of my power pivots, I am presuming that overwriting is the best way to do it. At the moment, I am copying and listing 100,000+ records from my latest extract to the current dataset.

Is there a better way? Thanks all.

 

[Russell Hauf]

Re: Best way to overwrtie

I'd use Power Query. Depending on what version of Excel you have, there may be different ways to do this. Excel 2016 you'd go to Data - Get Data - From Workbook. Then you can base your PowerPivot on this query, and just refresh the query when the data is updated.

 

[karlitob]

Re: Best way to overwrtie

Thanks for that.

Apologies - I don’t think my question was clear. But it related to how the background data is updated.

At the moment, my power pivots are based on data in a table of 100,000 records from a different data source. Each month I extract the same 100,000 records (as these records are continually modified by the end user) and a further c1,000. So every month I copy and paste 101,000 records into the table of 100,000 that I base my power pivots on (I’ve also a few formulas etc that I used in power query)

Copying and pasting 100,000 records isn’t fun. Any better way to overwrite data in excel.

 

[karlitob]

Re: Best way to overwrtie

Could I bump this please.
Thank you.

 

[Russell Hauf]

Re: Best way to overwrtie

Hi again - Power Query is made for just this situation - no matter how many records and what changes there are, it will pull in all of the data AND overwrite (or Refresh) what you have. Power Pivot should also refresh after you refresh Power Query.

 

[karlitob]

Re: Best way to overwrtie

Hi Russell,

Sincere apologies if it seemed as though I wasn't taking on board your advice - I just didn't read it correctly, but now I understand what you are suggesting.

Thanks again for your time. I will be back when I attempt this and probably break Excel!!