mass update if add new column in the table that import from folder

Copper Contributor

Hello everyone,


Aim to manage my files, I use Excel [Get Data] - [From File] - [From Folder] to import the filenames into Excel sheet.

Then I add one new column in the end of the table, fill some categories. I hope if there's new files saved in the folder, then I can refresh the Excel table and get the latest filenames list, and fill category for the new files. 

However, when I refresh the table, the rows are updated in a wrong way with my purpose, the cells of new column massed after update.


How can I keep the rows as I maintained, and let the cells in new column blank for new added rows.

I really need to solve this problem, appreciate if you can help.

1. I import the filenames from folder to Excel

2023-10-26 14_38_29-filenames.xlsx - Excel.png

2. I add new column in the end of the table, and fill some information

2023-10-26 14_40_05-filenames.xlsx - Excel.png


3. I save 2 new files in the folder

2023-10-26 14_40_59-C__Users_lmdgej0_Desktop_folder.png


4. I refresh the table, and cells massed...

2023-10-26 14_41_25-filenames.xlsx - Excel.png





2 Replies
best response confirmed by Joey-G (Copper Contributor)

@Joey-G Indeed. When you add a column outside Power Query the information in that column is no longer synchronized when you refresh the query and when new files were added. Google for "self referencing query power query" and you'll find many resources describing how to deal with the issue. Pick one that suits you best. You could use the file name as a unique identifier to merge the query with its previous output that has the column added to it manually.

Hello, @Riny_van_Eekelen
Thanks a lot for your quick reply.

Now I know there's not function for power query to sync other data just in worksheet, then I have to establish my base table and import into power query as reference.

Thanks a lot, wish you a nice day!