Create column not to be updated

[Torbenander]

In SharePoint I have a list of projects contain Project id/name/manager/participants

I've loaded this list into Excel. In Excel, I'have created a column for Januar/Februar/March..... In theese column I want to enter, how much each project manager/participant are allocation to each project. This works until the list of projects is changed in SharePoint., which changes to numer of rows in the list of projects. The manual entered data for the Month are keept in position, which corrupts the relationship between the data.

In the picture, the green data is from the Power Query and the white data in maunaul enter en Excel. If a projected is deleted the month data will be out of sync.

Can this be fixed en any way?
I tried to create the month columns in Power Query as blank and the enter data in excel in these columns. Refreshing data erase all data. Is is possible not to refresh specific columns with PQ? Other ideas are appreciated.

[v-stephen-msft]

Hi @Torbenander ,

One way to solve this issue is to create the month columns in Power Query as blank and then enter data in Excel in these columns. Refreshing data will erase all data, but it is possible to avoid refreshing specific columns with PQ. Another solution is to use VBA to automatically update the month columns when the list of projects changes.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.