Upserts M Language with IDs Power query 2013 RRS feed

  • Question

  • Hi,

    I have a problem to think about:

    I have a big data set that was put together a number of months ago, a sequential unique surrogate key was assigned to the data set.

    It has 3 key fields that could be used as a lookup lets call them X,Y,Z, so the data looks like

    ID X Y Z ...

    001 qwe erty yui

    002 asd fgh hjk

    003 zxc vbn bnm


    On refresh those existing records will turn up with new records. I want to use the fields x,y,z to do a look up with the current data set with the last data set, and if the record was in the previous data set pull the Id assigned to the previous data set, and if it's new create a new Id as part of a sequential unique ID.

    i.e if the record is an existing record pull the existing ID from the lookup previous data set, if the record is new assign a new sequential Id as the last Id assigned +1.

    I could do this is SQL, but not sure how i would do this in Power Query.

    Any ideas?

    Thanks in advance

    Thursday, July 28, 2016 6:34 PM


All replies

  • There are some workarounds people have come up with to accomplish this kind of thing, but it's not really a scenario Power Query is currently designed to support.


    Tuesday, August 2, 2016 10:34 PM
  • HI Ganjass,

    Does the following represent your refreshed dataset?

    ID Field1 Field2 Field3
    1 A A A
    2 B B B
    3 C C C
      B B B
      A B C
      A A A
      X Y Z

    If so, then would you expect the query result to be:

    ID Field1 Field2 Field3
    1 A A A<- delete
    2 B B B<- delete
    3 C C C
    2 B B B
    4 A B C
    1 A A A
    5 X Y Z

    So are dups allowed or do you want the original record deleted as above?  Also, what is in the ID field for new records?  Many thanks.

    Friday, August 5, 2016 12:20 AM