Formulas disappear after data refresh (self referencing table)

[PowetPlatformDev]

Hi,

In Excel, i have a table that is filled with data from an external source. My goal is/was that the values can be filled by the user manually. I made this work thanks to this thread i found on this board:

Power Query refresh removes manually entered data

Hello all, I've used Power Query to build a table and I want to enter data into the table after the fact. When I do this however and then refresh the table all the data goes away. The conditional formatting and data validation remain but no data. Here's some more info that may or may not...

www.mrexcel.com

I run into 1 problem now. The wish is that the values that are manually entered are filled with formulas. Unfortunately, when i refresh the data the hardcoded value from the formula gets saved, so i loose the previous filled in formulas.

Is there a way that the formulas stay in the cell(s) after a data refresh? Is this possible?

place	code	2025-01	2025-02
a	x	1	2
b	y	=SUM(C2)+2	=SUM(D2)+2
c	z	=SUM(C2)+2	=SUM(D2)+2
d	q	=SUM(C2)+2	=SUM(D2)+2

After my refresh the columns where a formula is now in the example, would just be the raw output without the formula.

Thanks in advance!

 

[jkpieterse]

The short answer: Anything that is pushed onto the grid from a query is static and cannot be a formula. A column which combines both manual input with formulas is not a very good idea anyway. I would have two columns: a calculated one and a manual override one.