irritating habit of power query (or maybe i dont get it) - but looks like a bug

[vanessafvg]

If you want to replace all blank values in power query ie either "" or null i usually create a custom column i.e

if field = null then "not specified"

else if field = "" then "not specified"

else field

as you can see below this created a custom column 

now when i go into edit it the format has changed to a conditional column

 fine no problem but the issue is always with the "" ie blank field which works fine in the custom column, .  **bleep** then fails because it wants you to put a value in there?

anyone else had that?

see example below, assoon as you click ok

it fails and if you put "" in it, it just creates around 6 of them which you must delete out of the advanced editor.

[GilbertQ]

Hi @vanessafvg

I know that it can be fustrating, and what I have done in the past is to go into the Advanced Editor and make my changes there when it tries to use the GUI for the Conditional Column.

[vanessafvg]

sure, thats what i do now, however i hope they fix it soon 🙂

[GilbertQ]

I agree, would be easier that way.

[MarcelBeug · Accepted solution]

There is some logic behind it: if you create an if ... then ... else statement in the formula, then Power Query doesn't know if the resulting code was created by adding a custom column or by adding a conditional column, however....

Edit: in your case, you can adjust your code like:

if [Field] = "" or [Field] = null then "Not specified" else [Field]

I have another example, which is even stranger:

= Table.AddColumn(PrevousStep, "Custom", each if Date.IsInNextYear([Date]) then "Next year" else null)

Gives you:

In this case, it is quite obvious that the code couldn't have been created with Add Conditional Column.

As a workaround, you can try and create something neutral that forces the Add Custom Column popup to appear.

In this example: concatenate "" to "Next Year":

= Table.AddColumn(PrevousStep, "Custom", each if Date.IsInNextYear([Date]) then "Next year"&"" else null)

[vanessafvg]

interesting, thanks @MarcelBeug