Latest Enterprise DNA Initiatives

Replacing nulls

Query Editor
#1

My original line in the query editor is
Table.Replace(#“PreviousStep”,null,"…",Replacer.ReplaceValue,{“ColumnName”})

I am hopin this replaces a null in a text column with …

Can I extend this to replace empty strings as well as null as I am anticipating bad data from the source system?

Thanks
E

#2

Hi @ells

Yes if you anticipate it that blanks(other random special characters too) may come in future data then you should consider extending this to make it future proof.

#3

Hi @ells,

Try something like this.

Table.TransformColumns( #"PreviousStepName",
    {"YourColumnName", each List.ReplaceMatchingItems( {_} , {{null, "..."}, {"", "..."}}){0} } 
)

I hope this is helpful

1 Like
#4

Hi @ells, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

#5

@Melissa
Many thanks, It is a very volatile environment and quality is not the key word so the better I can anticipate then the better the data quality going out.
Thanks
E

1 Like