Maintain edits to spread sheet after Power Query Save & Close

Regular Visitor

I have downloaded my CSV statements from my bank(s).
As you know, the CSVs are not consistent between banks or even different accounts from the same bank.
I have done a lot of work in Power Query to make them into proper data sets and remove, replace, reformat the CSVs so they are all consistent with one another and can be merged and/or appended to one master file that I can run reports on.
I am planning on using the same Power Query to format future downloads from the bank.
The problem is that I have a running total of the accounts that I would like to maintain over several years and the same going forward.
There are times that I may have to make changes to the Power Query to accommodate for changes that the bank may make or that I may decide to make for different reasons.
However, when changes are made to the Power Query, the changes I have made to my spreadsheet(s) and Pivot Table(s) will be overwritten by the update from Power Query.
Any ideas of how to retain the data I have changed (i.e. running v-lookup or x-lookup, or just replacing values in the resulting table(s).

Basil_58_0-1613146735968.pngBasil_58_1-1613146900240.png

As you can see, the bank "lumps" all deposits as "Deposit 1". I cannot edit the information on the bank side and I cannot edit the CSV file, because the next time I download it it will be overwritten. I would have to make the changes in the Spreadsheet version and I would like to maintain the changes I would make in the Spreadsheet.

 

2 Replies

@Basil_58 

 

First of all, it's good to see another person who downloads CSVs from various banks and incorporates them into a single database (you do that, right?). I do the same. I do it in Excel for Mac, where I don't have access to Power Query, however, so maybe I've been saved from the quandary you describe.

 

In my case also, though, I recognize that the various CSVs layouts from the various banks and credit card companies all are slightly different. I just manually get them all to the same set of five columns and then bring them into a master XLSX spreadsheet, doing so each month.

 

Once there, I go through all the transactions and assign budget categories, differentiating between income categories and expense categories......(adding columns of my own devising only in the master sheet). I then use a Pivot Table to produce a monthly report, showing month-by-month income and expenses in each category. I can do year--to-year comparisons as well. I never over-write anything from the past; the new data is always just being appended to the bottom of the historical record.

 

So that manual process takes a few minutes per bank account (6 of them altogether), but it's down to a good routine. The budget categorization takes longer, frankly.......

 

I just destroy the CSV files, once I've incorporated the data into the master Excel--is that not what you do? Why not? What's the reason for retaining them?

 

So help me understand what it is that you're doing that's different; what it is that you're trying to resolve.

@Basil_58 I believe what you describe is similar to what was dealt with in several postings in this forum not too long ago. See link below.

https://techcommunity.microsoft.com/t5/excel/combine-power-query-output-table-with-manually-maintain... 

This link describes the issue where the user wanted to manually add comments to a PQ generated table and keep these comments after future query refreshes. It starts with a technique I picked-up at one point, created a tutorial (basically for myself) and later on a better more compact solution was suggested. Not very straight forward, but certainly a neat trick. My guess is that you could use it as well.