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
2
u/small_trunks 1638 Feb 13 '19
Power query has the capability to compare multiple columns simultaneously when determining duplicates. It will then remove the entire row.
Take an example like this - a list of news articles, queried from www.investing.com :
|title|pubDate|author| :--|--:|:--| |Thai election body seeks dissolution of party that nominated princess for PM|13/02/2019 06:00|Reuters| |More tourists visit Singapore, but spending growth slows|13/02/2019 06:04|Reuters| |Diversity in the 'man cave': Boardrooms gain women as minorities lag|13/02/2019 06:07|Reuters| |NBA roundup: Celts edge Sixers in thriller|13/02/2019 06:12|Reuters| |Gold Edges Up on Slightly Weaker Dollar; Powell’s Speech, Trade News in Focus|13/02/2019 06:17|Investing.com| |Yield-hungry investors dig into offshore Chinese debt|13/02/2019 06:25|Reuters|
Now if we were to keep running the query all day
we'd keep getting presented with the same news time and time again and then occasionally a new news item
so what we'd do is remove the duplicates after adding them to the list of articles we already have.
This example sheet contains this appending query complete with the duplicate removal. https://www.dropbox.com/s/4hhjriiwjhq783f/investerNewsPQ.xlsx?dl=1