Forum Discussion
Power Query formula
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
6 Replies
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.
Why don't you do all such transformations in Power Query directly?
- good question if i knew how to do question #1, i would.
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.
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"
- Thank you. Apparently there's a way to do this within Power Query. Do you know how?
