PowerQuery allows you to use the Add Conditional Column wizard to build a simple IF function in PowerQuery. You can also just build the IF function in the Add Custom Column. The problem is if you want to edit a simple IF function, it always seems to revert to the Add Conditional Column setup even if you want the Custom Column setup (we often want to include some AND’s and OR’s in the IF). Below a trick to Switch off the PowerQuery Conditional Column wizard for easier editing of the formula.
Table of contents
YouTube Switch off PowerQuery Conditional Column wizard
Watch, like, subscribe
Add Conditional Column Wizard
As shown below, if you click on the ‘Gear’ icon for a simple IF function in PowerQuery, whether you built it or not with the Conditional Column Wizard it will open up in this format. Although very useful as a wizard, once you start writing your own PowerQuery formula, the Add Custom Column wizard is easier to write in.
Force the Custom Column wizard to open an IF
If Excel sees a straightforward IF function in PowerQuery it will bring up the IF wizard. To stop this and make it show the formula in the normal Custom Column, you just need to make the IF function slightly more complex directly in the formula bar.
As shown below, by just changing the null (compare to image above) to 1 + 2, when you click the ‘Gear’ icon, this extra ‘complexity’ means that the normal custom column wizard opens up.
You can now replace the 1+2 with null and then work on the rest of the formula in the Custom column settings.
Related
Learn about PowerQuery in Excel
Where has the Text Import Wizard in Excel gone
Easy fix for promoting headers when name can change issue in PowerQuery
Overflow error in Excel PowerQuery
Begins with, Ends With, Contains missing in PowerQuery Filter