Issues with removing data in column using Text.remove

[Anonymous]

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.

[Anonymous]

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)

[wdx223_Daniel · Accepted solution]

NewStep=Table.TransformColumns(PreviousStepName,{"ColumnName",each Text.Combine(List.Select(Text.Split(_,";"),each Text.StartsWith(_,"PD-")),";")})

[Anonymous]

Thanks this did the job!

[jennratten]

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"

[jennratten]

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-"))