Remove values from cell containing specific character

[winterbloom]

Hi all,

I have got a large table with many columns. One of these columns contain some values that I need, some nulls and some with values I need to remove.

Here is a snippet of the column in BI data view:

I want to keep the values shown in the background. Meanwhile there are thousands of rows where the values in this column is gibberish. I need these values to be blank/nulls instead.

One option is to replace all values where the special character "²" occurs. Another option would be to remove values where the string exceeds 10 characters.

How can I do this in Power Query?

TIA

[PhilipTreacy · Accepted solution]

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

[ronrsnfld · Accepted solution]

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:

[PhilipTreacy · Accepted solution]

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

[PhilipTreacy]

Hi @winterbloom 

This query filters out any row that has the ² character in it

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlGK1YlWMjUztwAzDAxB8NAmI3MzI2OopAEIGBmYAEUNTZGEDA0ObQKShgZgIZBZEGNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([Value], "²"))
in
    #"Filtered Rows"

To implement this, click on the Drop Down Arrow in the column header, then click Text Filters -> Does Not Contain

and enter this

Regards

Phil