Excel Power Query return formula in especific columns when refresh

0
votes

So... I have a worksheet that some parts of it came from Power Query and others parts are columns that I created manually. Nevertheless, all the sheet is only one table. Ok. The thing is in some columns I have put some formulas, but eventually, I need to put manual data, but when I refresh the query some columns return de formula and others do not (????). It doesn't make any sense. There's no specific difference between them. I tried to take the formula as range, like that:

before:

=IF(SUMIFS(BS_Boletos!F:F;BS_Boletos!A:A;**[@ID]**;BS_Boletos!G:G;"")=0;"";SUMIFS(BS_Boletos!F:F;BS_Boletos!A:A;**[@ID]**;BS_Boletos!G:G;""))

after:

=IF(SUMIFS(BS_Boletos!F:F;BS_Boletos!A:A;**C3**;BS_Boletos!G:G;"")=0;"";SUMIFS(BS_Boletos!F:F;BS_Boletos!A:A;**C3**;BS_Boletos!G:G;""))

But still the same.

I've kind of found one video on Youtube that, but it does not work.

When I fill the table with data, this data will disappear when refreshing the table: how can I refresh the query without losing the manual data?

Someone can help me with that?

1
excelpowerquery
The question is not easily read, but if you are asking about powerquery tables where manual data is preserved after refresh, see exceleratorbi.com.au/self-referencing-tables-power-query – horseyride
@horseyride, that's exactly what I want, but I didn't get how this article help me. I just wanna put some data in some cells that have formulas and when I refresh the data still there, but the formulas keep coming back in some columns (not all of them (??)). I have another worksheet that I do this and works fine. – Ericmss
Sorry. You dont provide any example of your input data, don't show the desired output, and there is no way for anyone to tell what you want us to help with – horseyride
@horseyride, Sorry, I'm non-native speaker of english, so my question is not easily read, like you said. The thing is, e.g.: The sheet has columns that came from Power Query and columns inserted manually, but all in one table. In some collumns (inserted manually) I put some formulas, but I need to put data manually in some cells. What I want is when I refresh these cells remain with data that I input. What is happening now is that some columns of these inserted manually the formula came back and some others does not. The difference between them one of it is Date and the other Currency. – Ericmss

1 Answers

0
votes

Formulas will remain only in columns inserted manually (outside Power Query) - those will be filled to all rows after refresh.

If you still need for some reason to have formula defined in PowerQuery then you can type it to column as string but after loading you will have to apply that string as a formula - most likely with VBA.