How do i create a Power Query custom column AND insert an excel formula into each cell of the final table?

Question

I want that when the table is created there will be a fully functioning excel formula. If I try with "... each "=[@[Prog.]]" there will insert only the text not the formula.

P.S.: I'm new to this so, excuse me if this is basic.

Answer 1

Find and Replace "=" with "=" and all text will be converted to formulas.

Answer 2

The above answers are correct that the formulas do not automatically recalculate, even though they are in the cell correctly. A workaround/trick that I found was to use -Find/Replace to trigger a recalculate on every cell in one step. Go to one of the cells with the formula; to copy the entire formula. to open the Find/Replace dialog. in both the Find What and Replace With boxes in the dialog. Click on Replace All. All the cells except the one you are in will recalculate. Then, exit the cell you are in, and it will also recalculate.

It seems stupid, but it works.

Answer 3

From what I understand, you can create Excel formulas in Power Query and pass those to your Excel worksheet but the worksheet won't automatically recalculate. This earlier post addressed the same thing.

I tried this out by creating a table with two columns: "Column1" and "Column2", with the numbers 1 & 2 in each, respectively. Then, I loaded the table into Power Query and created a new column, "Custom", with the formula, "=" & Text.From([Column1]) & "+" & Text.From([Column2]). (To be clear, the = here is not the = that is already populated when the new column dialog box pops up, it's additional.) Anyhow, I got this table from that formula:

Then, when I clicked "Close and Load," my Excel worksheet was loaded with this:

Notice it looks just like text, but in the formula bar, it looks like this:

Notice there isn't a ' before the =, so it's a formula. If it were just text, it would be '=1+2 instead of =1+2.

Since the cell doesn't automatically recalculate, I had to click the cell I wanted to update, and then click in the formula bar and then press enter. That gave me this:

I tried to use F9 to manually recalculate, but it did not work.

Also... Every time I refreshed my query, my worksheet was set back to just the text-looking formula (i.e., =1+2) and I had to re-recalculate manually. That would be a real pain to have to do that for a lot of cells each time you refresh your query.

@Umut K posted a VBA-based workaround to trigger refresh all in the earlier post, but I think what you are asking to do might be more trouble than you're looking for.