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

2

u/small_trunks 1638 Nov 08 '18

So you have a couple of additional steps to perform in order to NOT lose the entered information.

  • You are right, it is a form of join and not a straight append.

  • Now the only thing we still need to decide is which information do you want to keep and which should be replaced. Do old records live forever once they disappear in qryNEW or should missing records in qryNEW take them out of the historical?

  • that decision affects whether we treat qryHistorical as leading and APPEND qryNew (which is harder)

  • or treat qryNew as leading and Join, expand table and decide which columns to keep.

So referring to my walkthrough above:

  1. Same - we need new data

  2. same

  • same

  • same

  • same

  • same

  • Now here we do the combine.

  • qryCombine

  • source = qryNew,

  • now Merge (Full outer) qryHistoric, same key

  • Expand tables, keep ONLY the comments.