Forum Discussion
Add trim after Split
I am using this to Find/Replace a table
let
Repl = Table.Buffer(ReplacementsTable),
Text = Text,
Result = Table.AddColumn(Text, "Changed Text Expected",
each Text.Combine(
List.Transform(
Text.Split([Text], "|"),
each
try Repl[Replace With]{List.PositionOf(Repl[Word To Replace], _)}
otherwise _
),
"|" )
)
[[Changed Text Expected]]
in
Result
I am splitting a column on a pipe delimiter
Is it possible to add trim at this point?
Text.Split([Text], “|”),
I have strings that look like
the|cat sat |on the mat
find replace
cat sat >> bear
does not get replaced because of the space after the "sat" in “the|cat sat |on the mat”
Thanks
Use below in place of Text.Split([Text], "|")
Text.Split(Text.Replace(Text.Replace([Text]," |","|"),"| ","|") "|")
4 Replies
Use below in place of Text.Split([Text], "|")
Text.Split(Text.Replace(Text.Replace([Text]," |","|"),"| ","|") "|")Anonymous
Do you have limited scenarios then maybe try replacing value, else could you please share some sample data (5-10) rows and the expected output.
You can replace
Text.Split([Text], "|")with
List.Transform(Text.Split([Text], "|"), Text.Trim)to trim each string after separating by "|".
Here's another implementation that uses List.ReplaceMatchingItems:
let Repl = Table.Buffer(ReplacementsTable), Text = Text, Result = Table.AddColumn( Text, "Changed Text Expected", each Text.Combine( List.ReplaceMatchingItems( List.Transform(Text.Split([Text], "|"), Text.Trim), List.Zip({Repl[Word To Replace], Repl[Replace With]}) ), "|" ) ) in Result
