Updating several fields in one table from new table

[DocMCSE]

Hi all, I'm new here (for posting at least, have gotten many answers in Google from these forums) but have been around Excel quite a long time. Today I have a workbook where historical trouble ticket data is saved in one tab and recently added tickets (and other tickets that are still in progress). Today I use VLookup to update 2 of the fields in the historical table (current ticket state and resolution set). Since the second table does not include the older tickets we manually force set the 2 lookup fields to the final value they had (else the dreaded #NA). While this is working just fine it tends to get slower and slower the more data we have in the historical table.

My first thought was to recreate the tables in MS Access which I did and using the following query was able to do the same updating we did in Excel with VLookup and it runs very quickly but not everyone in the team has the MS Access tool. Here is my MS Access query:

UPDATE [Open this month]
 LEFT JOIN [SDEL Daily]
  ON [Open this month].[Ticket Number]=[SDEL Daily].[Ticket Number]
   SET [SDEL Daily].[Ticket Number] = [Open this month].[Ticket Number]
   , [SDEL Daily].[Ticket State] = [Open this month].[Ticket State]
   , [SDEL Daily].Severity = [Open this month].[Severity]
   , [SDEL Daily].[Reported Product] = [Open this month].[Reported Product]
   , [SDEL Daily].[Work Queue] = [Open this month].[Work Queue]
   , [SDEL Daily].[Asset Id] = [Open this month].[Asset Id]
   , [SDEL Daily].ATTUID = [Open this month].[ATTID]
   , [SDEL Daily].[Location Id] = [Open this month].[Location Id]
   , [SDEL Daily].[Ticket Opened Date] = [Open this month].[Ticket Opened Date EST]
   , [SDEL Daily].[Resolution Set] = [Open this month].[Resolution Set Name]
   , [SDEL Daily].[Cost Center] = [Open this month].[Cost Center];

So I was then required to try to replicate the faster updating I did in MS Access but do it in Excel. I've tried several ways to do this using Power Query but all I ever get is the fields in the first and second tables all on one row (expectation is to update first table if data in second, else leave it static and add any new rows the second table has that did not yet exist in the historical table.

Hopefully this is not too vague and someone can see what I'm wishing to do here. If you can help I'm already very pleased (even if you cannot help) just for you to have gotten to the end of this long posting.

DocMCSE

 

[Matt Allington]

I don't think i fully understand, but conceptually, I think you are trying to take a history table, find matching records in an active table, and return some extra data/status in the history table. If this is right, it sounds straight forward to Power Query.
Load boh tables and set to "connection only"
crewte new query with reference to the history table, then merge with the active table, expand one of the columns to see which rows match, then add a new custom column to add your new data.

 

[DocMCSE]

Let me try and clear this up a little bit. In the first table (The MS Access query named is "SDEL Daily" but we will refer to it as Archive table) are trouble tickets going back several years (most of which are resolved and closed). The second table contains only the most recently closed tickets (past 30 days only) and all open in progress tickets (in Access this table is the "Open This Month" table but we will refer to is as the "New or in progress" table). Several of the fields change day to day in the "New or in progress" table plus newly opened tickets are also included in this table as well. All of the columns in both tables are identical so what I'm attempting to do is the following 3 things.

1) Older tickets that have long since closed in the Archive table and therefore no longer will be in the "New or in progress" table are to be kept intact
2) New tickets in the "New or in progress" table will be appended to the Archive Table
3) Tickets currently in the Archive table that also are in the "New or in progress" table should have the "New or in progress" data update the record in the Archive table

For 1 and 2 this is a pretty straight forward query (appending new tickets from the "New or in progress" table with the Archive table). Which leaves me to discover how to update matching tickets in the "New or in progress" table so that that data replaces the out of date data the Archive table has for those tickets.

DocMCSE

 

[DocMCSE]

So I took another look at the daily source data only to discover a column (that we never imported into Excel) is in there giving the last date the ticket got updated. By simply adding this column to my Archive table (initially setting the last updated time I'd not had from historical data to some older date) and using it to sort and remove duplicate rows now I have something that performs as expected. Would have been nice if I could then over write the Archive TAB's data from the Append query but a quick copy/paste did that for me.

DocMCSE