Forum Discussion
Remove values from cell containing specific character
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
Hi winterbloom
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:
3 Replies
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
Hi winterbloom
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: