Power Query formula

[Rex-Delson]

Question 1: In Column H is a simple formula to show the variance between columns G and F.  How can I show a "null" in H when there is nothing in G?

Question 2: Column F has a simple formula that multiplies values in column E by 30.  When I try changing the formula in a cell  of column F, the entire column changes to the new formula. How can I change a formula without affecting the rest of the column? 

Example

[#2]

@Rex-Delson 

Answer 1: You can use IF funtion, if the Column G has no value.

=IF(G1="","null",G1-F1)

 Answer 2: Use the UNDO or CTRL+Z (twice) or, once it automatically copied the formula you typed, an icon will appear once you press the ENTER. Click on that Icon, and it will show an options, then simply choose "Stop Automatically Creating Calculated Columns"

[#3]

@Rex-Delson 

Why don't you do all such transformations in Power Query directly?

[#4]

good question if i knew how to do question #1, i would.

[#5]

Thank you. Apparently there's a way to do this within Power Query. Do you know how?

[#6]

@Rex-Delson 

For such sample:

- stay on Add Column tab

- select Estimate column, keep Ctrl key, select Aprox column, release Ctrl

- under Standard in drop-down select Subtract

New column will be generated. If nothing in any of source columns (aka null) it also returns null. Returned to the grid table will have empty cell here.

If you prefer some text you may modify generated formula in formula bar with

, each if [Estimated] = null then "nothing is here"  else [Estimated] - [Approx]  ,

If Approx could be empty before subtraction step select column and replace value,  null on 0. 

[#7]

@Rex-Delson 

By the way, if for returned by PowerQuery table you manually add data to new columns (e.g. Angel) the won't be synced on refresh, it doesn't work such way. Workaround is possible, depends on how do you build your data.