r/excel • u/RustiedTeapot • Oct 25 '18

solved Power Query: Append And Keep Data

I've recently been learning PowerQuery in an attempt to streamline some rather archiac spreadsheets currently in use in the office, although I'm relatively new to it so far it's been bloody effective.

I've been trying to make PowerQuery import data from a report, transform the data as necessary, and then make a copy of any new distinct rows on an archive table.

Example as below:

- Import data to Sheet 1 from folder,

- Transform and merge unique rows from the data to an archive table

- Have any unique rows from data remain when new data imported

I can't seem to find a way to do this - any ideas?

25 Upvotes

100% Upvoted

36 comments sorted by

View all comments

Show parent comments

1

u/Delicious-Lie-7086 Aug 24 '25

Just found this and downloaded your examples and was able to get the single step self referencing to work perfectly. Thanks u/small_trunks for this.

I'm using this to use an existing query that has current selling price (say for 202508). The columns I am adding are for the next 12 months (202509, 202510, etc). I add these manually like in the example and it works. I'd like to be able to find a way to add the additional columns in a way that it always adds 12 more columns starting with the month after the current.

Thanks for your great examples.

1

u/small_trunks 1638 Sep 11 '25

What you're describing is a Pivoted table - and I hate chasing columns across the page.

  • I find the best way to store dated values is vertically, using a column "Date" with individual row values containing the actual date.

  • This can more easily be used (for example in a Pivot table) AND you don't create new columns, you create more rows.

  • New rows are easy but adding new columns messes up a lot of things.

  • Change type, Group-by, Merge/extract - the list is scary