0

I am using Power Query in Excel 2016 to combine data from 12 different workbooks within the same folder system into one table, and need to add an additional column in the master table that tracks the status of each row. However, when I refresh the data, the Status column does not follow the rows to which it is initially applied.

I have already looked at [ Inserting text manually in a custom column and should be visible on refresh of the report ] but this solution only works with a unique ID column. Because each of the 12 workbooks is edited separately and because there is no single column that can be guaranteed to have unique values between all of the different spreadsheets, I don't have a key to join the data to the additional column.

VeryPerfect
  • 1
  • 2

1 Answers1

0

I believe there is always a way of finding a Unique ID. If you can get your head around this, it is not that difficult to solve your problem.

See my below example, I used three sample workbooks saved in a Test folder. Depends on the way you add them to the query editor, in my example I used From Folder and follow the prompts without making any changes and combined the tables automatically. Once combined there is a Source.Name column automatically added. I suggest to leave this column in your output table as it can form part of the Unique ID if your data is highly identical across the workbooks.

An optional step (not in my screenshot) is to add an Index column and concatenate the index number with a product/task name so it can make that specific line of data entry even more unique.

Once you added the Status column with data entered manually on the master table, load the master table back to query editor.

Then go back to the original query (Test (Input) in my example) and merge it with the reloaded output query. See my screen-shot for how to 'uniquely' merge the two tables.

The rest is self-explanatory. I think the key is finding elements of the Unique ID and incorporate it in the merge part.

Solution

Let me know if you have any questions. Cheers :)

Terry W
  • 3,199
  • 2
  • 8
  • 24
  • Thanks for posting such a detailed response. I think that I'm missing something, though, because when I Merge the tables, I do not get that extra column in the original table. I wind up with a total of 3 tables - the original, the Output, and a Merge1 table. The original and the Output table both have the Status column, but it still isn't linked to update its position when additional rows are added to the source documents. – VeryPerfect Aug 23 '19 at 15:40
  • @VeryPerfect let's call the first table generated by your first query Table1, which is the master table that you want to add the status column. You add the column and then load this table back into query editor, it becomes a **new** query, let's call it query 2. You do not change query 2, but **go back to query 1, and merge it with query 2**. When doing the merge you can select multiple columns that **identifies** that line of data, without seeing some sample data it is up to yourself to find out what can be the identifiers. The rest is self-explanatory. – Terry W Aug 25 '19 at 08:05