Keeping custom column synced with external data query RRS feed

  • Question

  • Hi guys,

    Here's my scenario:

    I'm using Power Query to APPEND two external source queries from 2 different ticketing databases. 

    I have 3 queries total,

    1 First Ticketing System Query (Connection Only).

    2. Second Ticketing System Query (Connection only).

    3. Combined (Appended) Table showing data from both systems.

    Here's my problem:

    Now, I want to be able to add my own CUSTOM columns to Query 3 (The Combined table) to account for time worked on that specific ticket. 

    When I add any CUSTOM data, upon a refresh of all the queries, the data becomes out of sync and my custom column is no longer associated with that specific Ticket number.

    Now, I understand that I need to use some form VLOOKUP, or another table with a fourth query here, but I'm not finding any help online doing this with my setup (That is, using Power Query). Because remember, I'm appending two different queries into one. 

    Another thing to note, I'm using slicers and am sorting my data by "Last Modified Date." So this is what causes my data to shift up which break the sync with the custom column. 

    I appreciate the help and hope I've articulated my problem clearly enough. 


    Tuesday, May 17, 2016 11:58 PM


All replies

  • Hi flome. The simplest way would be to add a custom column in Power Query. What is the data you're needing to add? Is it a calculation, or something you're manually entering?


    Wednesday, May 18, 2016 5:22 PM
  • Hi Flome,

    I read your request. Did you ever succeed with this ?

    I've pretty the same problem: I would like to get some data from external source and add simply some additional informations into an additional column (some useful informations which are unfort. not available in the external data source)

    If I fill the column with these informations they only persist as long as I do not any Query Refresh. But after refresh and table update the row assignment is completely lost.

    Adding some custom column is no solution, cause this additional column should be a free-text column and completely independent from any other external data source column.

    Any solution/idea ?

    Thanks catrice

    Thursday, March 5, 2020 12:31 PM
  • BTW for sure I could program some workaround with some macro which e.g. does some lookup on some unique data and re-assigns the rows after query update. 

    But I would like to know if there is some more smart standard solution for it available within PowerPivot...

    Thursday, March 5, 2020 12:35 PM
  • Hi catrice. Have you tried modifying the External Data Properties for the Excel table that PQ creates?


    Thursday, March 5, 2020 6:55 PM