Forum Discussion
Issues with removing data in column using Text.remove
Hi all,
I’ve spend a lot of time in fixing the error (youtube, this forum). What i did?: I’ve imported a table into excel, for which I want to transform (delete) data with power query. I only want to keep data with values “PD-*” So * is a wildcard, hence can be each number.
Below are the values in a coumn, the only value I want to keep from below example is PD-35:
Column values: PR-00001;WL-00009;PD-35
Power Query:
= Table.TransformColumns(#"Duplicated Column", {{"Linked Issues - Copy", each if Text.Contains(_,"PR-") then Text.Remove {"P""R""-""0""9"} else if Text.Contains(_,"WL-") then Text.Remove {"W""L""-""0""9"} else _, type text}})
After executing above Query its throwing the following error:
Error:
Expression.Error: We cannot apply an index of type Text.
Details:
Value=[Function]
Index=P"”R"-"0"9
So simply I get an error in all fields containing either of above values I also tried to play with Text.BetweenDelimiters or “replace” but I cannot get it to work. I hope someone can help me out.
NewStep=Table.TransformColumns(PreviousStepName,{"ColumnName",each Text.Combine(List.Select(Text.Split(_,";"),each Text.StartsWith(_,"PD-")),";")})
5 Replies
Hello - if you'd only like to keep rows that have a value beginning with 'PD-' in the 'Linked Issues - Copy' column, you can do it like this:
Table.SelectRows(#"Changed Type", each Text.StartsWith([#"Linked Issues - Copy"], "PD-"))Thanks for you swift reply. The query applies a filter, i would like the query to remove all values that does not start with "PD-"
example of values in column
Example Expect after query removed data PR-00001;WL-00009;PD-35 PD-35 XL-00001;PD-0016;WL-00010; PD-0016 XL-00001;PD-0016;PD-00015; PD-0016;PD-00015; (Exeptional case, above two is most important) Sure, no prob. Here you are.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCgjSNQACQ+twHzDD0jrARdfY1FopVidaKcIHKgkUA1JmUEWGBjikwbSBIUx3ZBSEEeCCEIAoARkQCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Example = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Example", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine ( List.Select(Splitter.SplitTextByDelimiter(";", QuoteStyle.None)([Example]), each Text.StartsWith(_, "PD-")), ";"), type text) in #"Added Custom"NewStep=Table.TransformColumns(PreviousStepName,{"ColumnName",each Text.Combine(List.Select(Text.Split(_,";"),each Text.StartsWith(_,"PD-")),";")})
