Excel Power Query: self-referencing manually entered data

[aduroche]

Hi!

I am successfully doing power queries to ingest .csv bank and credit card statements from 3 different institutions. I then merge them into a common, uniform Excel Table structure for personal budgetary spend tracking. All that is working A-1.

The issue at this point is that I need to manually add a few columns where the queried data is analyzed via formulas or manual entries to tie them with the spending categories.

I've been searching this topic for a while now, and basically there are 3 articles that sum this up, including this Mr. Excel board (links below).
The common thread with all 3 articles is that they go a few years back and from what I have understood, Power Query in Excel has been changing a fair bit over time.

The issue with the first 2 articles/options is that every time you trigger a data refresh, the columns manually added outside of the original query end up being duplicated.
Both articles basically say to ignore these and delete them... Which is fine once, but a bummer on a regular basis.
Is there any tip to not end up with this duplication?
And also, the formatting of the table has been slightly modified from one of the proposed templates in Excel (different font type, size and color). The problem is that at every refresh, the manually added columns change format to the original template's. Any way to prevent this?

Article #1 here
Article #2 here

The 3rd video dates a while back (2016), combines formula tweaks within PQ as well as steps from articles #1 and #2 above.
The difference with this one is that there are no column duplication upon refresh.
I haven't gone there yet, being fairly new with Power Query, formulas are beyond my league and I'd like to keep this workbook simple...
But I'm thinking, are these formula tweaks still necessary today, or were they only necessary to hack query merges 6+ years ago?

Thanks in advance for any clarification you can provide. --Alain

 

[aduroche]

EOD update after testing all kinds of options...

So, Marcel Beurg's method (3rd link) ends up with the same outcome as with the other 2... except it's more complicated. Contrary to his video, where no columns were being duplicated upon "Refresh All", it turns out now they do get duplicated. So I guess that answers my original question.

The other thing I realized tonight is that, all the formulas added in 3 of those 4 manually added columns get zapped at the next Refresh All. All the data gets consolidated to the result values. So, going forward, every time new data gets added, I must manually enter my formulas again for those 3 columns in the newly added rows.

Got some progress on the table reformatting front. In:
Table Design > External Table Data > Properties > Preserve Cell Formatting
Unchecking that option appears to solve my issue. No more reformatting.
I guess the logic here is we are not preserving the cell formatting from the source data, even though the source is a .csv file? And so, we are leaving the current formatting intact?
It was just weird that things would reset to the initial Table template I had picked... Which again, is unrelated to the "External Table Data"?

 

[aduroche]

EOD update after testing all kinds of options...

So, Marcel Beurg's method (3rd link) ends up with the same outcome as with the other 2... except it's more complicated. Contrary to his video, where no columns were being duplicated upon "Refresh All", it turns out now they do get duplicated. So I guess that answers my original question.

The other thing I realized tonight is that, all the formulas added in 3 of those 4 manually added columns get zapped at the next Refresh All. All the data gets consolidated to the result values. So, going forward, every time new data gets added, I must manually enter my formulas again for those 3 columns in the newly added rows.

Got some progress on the table reformatting front. In:
Table Design > External Table Data > Properties > Preserve Cell Formatting
Unchecking that option appears to solve my issue. No more reformatting.
I guess the logic here is we are not preserving the cell formatting from the source data, even though the source is a .csv file? And so, we are leaving the current formatting intact?
It was just weird that things would reset to the initial Table template I had picked... Which again, is unrelated to the "External Table Data"?

Final update.
Not sure when this happened, but no more repeated column insertion everytime I refresh all.
I recall having to clean-up the query pretty much at the same time I disabled the "Preserve Cell Formatting" option, because of an error that had been reported running the query.

 

[raskolnikov]

Delete the new columns on the first load and they shouldn't reappear.

 

[aduroche]

Delete the new columns on the first load and they shouldn't reappear.

Thanks raskolnikov. In the dozens of tests I did, I can sware this was not always true. There were instances where the columns did get reinserted/duplicated at every "Refresh All" within the same session. The only difference they'd be numbered incrementally (e.g. "Comments" became "Comments2" which became "Comments3", etc.). So, not sure what exactly ended up working for me as I said, but I'm glad it's now working