Replacing certain items within a column

[Jenolski]

Hello,

I'm currently doing some data cleaning but I stumbled into a problem. I have a column filled with names such as John, Willem, etc. But within that same column, I also have data in the form of E40 or N804. The items starting with E or N followed by a number should be replaced by null so I can fill down the names. 

Is there an easy way to accomplish this?

Thank you in advance! 

[Jenolski]

Additionally, E and N can be followed by different numbers. So not only the examples that I earlier gave. 

[camargos88 · Accepted solution]

Hi @Jenolski ,

Try this code:

let _value = [Column1] in
if
(
Text.Start([Column1], 1) = "E" or
Text.Start([Column1], 1) = "N"
) and List.Count(List.Select({"0".."9"},
each (_) = Text.Range(_value, 1,1))) > 0 then null
else [Column1]

[Jenolski]

Works like a charm, thank you! 

[Greg_Deckler]

Well, we will need to find something that uniquely identifies those rows. Perhaps the presence of a comma (,)?