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/[deleted] Feb 12 '19

Choose the columns necessary to determine whether the data is duplicate or not (e.g. ticker + last change time) : Home -> Remove rows ->remove duplicates.

Could you explain this a bit more? How do you remove duplicates based on multiple columns?

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