Remove values from cell containing specific character

Hi winterbloom 

Download example PBIX file

Just re-read your question and think I have misunderstood what you want.

If you want to keep all the rows and just remove the values with a ² in them, or that are more than 10 characters, add a Custom Column with this code

 = if Text.Contains([Value], "²") or Text.Length([Value]) > 10 then null else [Value]

Then delete the original column.

I'll leave my initial reply as an FYI.

Regards

Phil

In the PQ Advanced Editor, add a step that transforms the contents of cells in that column to null if they contain "²" or have more than ten characters.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlGK1YlWMjUztwAzQCKHNpmawTkgGUtDiMJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

//Transform cells that contain more than 10 characters, or contain ²  to null
    #"Transform garbage" = Table.TransformColumns(#"Changed Type",{"Column1", 
        each if Text.Length(_)>10 or Text.Contains(_,"²") then null else _})
in
    #"Transform garbage"

becomes: