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.
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)
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)
Helpful resources
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
