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 wa...
NewStep=Table.TransformColumns(PreviousStepName,{"ColumnName",each Text.Combine(List.Select(Text.Split(_,";"),each Text.StartsWith(_,"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"