Error with Text.Trim

[DemoFour]

Hi people, 

I am trying to use a list with Text.Trim to remove punctuation and line breaks, but I am getting an error. 

In the Gill Raviv exersize 11-4, he uses Text.Trim and amended the code from: 

= Table.TransformColumns(#"Trimmed Text",{{"Single Line Tweet", Text.Trim, type text}})

to 

= Table.TransformColumns(#"Expanded Stop Words",{{"Single Line Tweet", each Text.Trim(_, #"Punctuation and Line Breaks"), type text}})

This then removes the characters and line breaks etc. from the text by use of the list. 

However in the real world I get an error = 

Expression.Error: The value isn't a single-character string.
Details:
Value=..

I have added .. into my list so I am at a bit of a loss as to the error, and as I am only just branching out into M, any guidance on solving the error would be much appreciated. 

Thanks 

[HotChilli]

Right, so the list needs to hold single text values.  It has lots of multi-character values

[DemoFour]

Can you eloborate a bit please. 

The list of Punctuatuation is below. I added the .. ... !! !!! !!!! becasue of the source tweet text containing this to try and fix the issue. 


The list 

= {" ", "~", ",", ".", "..", ". .", "...", "....", ".....", "?", "!", "!!", "!!!", "!!!!", "!!!!!", ", ", (", "), "[", "]", "{", "}", "%", "^", "&", "*", "+", "=", ":", ";", "|", "<", ">", "/", """, """, "\", """", "#(tab)", "#(cr)", "#(lf)"}

In the Raviv book, the text is all nice and neet of course, real world data is unpredictable and not following an easy format. 

I did not think this would be such an issue, to learn some M and to try out some textual clean up and analysis for an upcoming project. 

[AlexisOlson]

@HotChilli is correct. The Text.Trim function only takes a list of single characters.

For example, this does not work

But this does:

If you clean up your list to remove multi-character strings then it should work. For example, try this definition:

{" ", "~", ",", ".", "?", "!", "(", ")", "[", "]", "{", "}", "%", "^", "&", "*", "+", "=", ":", ";", "|", "<", ">", "/", """", "\", "#(tab)", "#(cr)", "#(lf)"}

[DemoFour]

@HotChilli @AlexisOlson 

Thanks guys, so the previous error I was getting because the column had multiple strings. That makes sense. 


Can you suggest a robust way to remove the multi stings, or is it a case of manually using replace text for all combinations - I feel that this is not very scaleable, so may brake the query. 

Thanks for taking the time to respond. 

[ronrsnfld]

What are you asking?

Text.Trim(Text, Trim) is going to remove all the characters in the Trim list from the beginning or end of Text. If your string ends with "!!!" and you have "!" in Trim, it will remove all of the "!"'s.  You only need a single character listed.

If your use case is different, please clarify. For example, if the characters in Trim are not at the beginning or end.

[DemoFour]

@ronrsnfld 
thank you for your response. 

I have !!! etc. at the end of sets of text, So you are saying that just having ! in the list will remove all of them. 

There are all sorts of random punctuations as it is twitter data and people do strange things to put emphasis on the point. 

Ok I will revise and see what happens

[ronrsnfld]

Yes, even combinations. So if the string ends with "...!!!??", you can use trimList={".","!","?"} and it should remove all of them. If it doesn't, as I noted in your first tweet example above, it is because the character entered in trimList is different from the character actually there.

Your first line looks like it ends with "â€".  However, it actually ends with "”" where the very last character is code 157, and the pound sign, instead of being code 128, is actually a unicode character number 8364 (an HTML code). So you would have to include the codes for all of those particular characters in order to trim them off the end. Otherwise it will stop trimming at the first character from the end it encounters that is not in the trimList.

[DemoFour]

@ronrsnfld 

I had postulated that the issue was with HTML code, I am not a coder so I was looking for lists of HTML etc. to try and think up a scalable list to use to remove all of this from the tweets. 

That is a very good point and has helped me to find more of a resolve and understand the M language and using more advanced query editor usage. Thank you for your reply. 

[ronrsnfld]

In your trimList, you can include  Character.FromNumber to generate the "strange" codes.

[DemoFour]

@ronrsnfld You must have read my mind, as I was struggling for the codes to register and not be text string in the list! 

[AlexisOlson]

Another potential approach would select only the characters that you want to keep and throw everything else away.

[HotChilli]

"...", "!!!!"

are multi-character strings.

"."   or   "!" are single character 

[HotChilli]

The documentation on Text.Trim is sorely lacking regarding the second parameter.  The implication from the code you posted is that

#"Punctuation and Line Breaks"

is a list which holds single-character text values.   I don't know what's in there currently but ".." will probably break it

Sorry about the red text!

[HotChilli]

We'll need to see some sample data +

#"Punctuation and Line Breaks"

list

[DemoFour]

@HotChilli 

It is a list 

= {" ", "~", ",", ".", "..", ". .", "...", "....", ".....", "?", "!", "!!", "!!!", "!!!!", "!!!!!", ", ", (", "), "[", "]", "{", "}", "%", "^", "&", "*", "+", "=", ":", ";", "|", "<", ">", "/", """, """, "\", """", "#(tab)", "#(cr)", "#(lf)"}

Data is just junk twitter data so happy to share. 

let
    Source = #"20230202 Twitter Data",
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}}),
    #"Lowercased Text" = Table.TransformColumns(#"Changed Type",{{"Tweet", Text.Lower, type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Lowercased Text",{"Tweet ID", "Tweet"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Tweet", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Tweet.1", "Tweet.2", "Tweet.3", "Tweet.4", "Tweet.5", "Tweet.6", "Tweet.7", "Tweet.8", "Tweet.9", "Tweet.10", "Tweet.11", "Tweet.12", "Tweet.13", "Tweet.14", "Tweet.15", "Tweet.16", "Tweet.17", "Tweet.18", "Tweet.19", "Tweet.20"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"Tweet.1", "Tweet.2", "Tweet.3", "Tweet.4", "Tweet.5", "Tweet.6", "Tweet.7", "Tweet.8", "Tweet.9", "Tweet.10", "Tweet.11", "Tweet.12", "Tweet.13", "Tweet.14", "Tweet.15", "Tweet.16", "Tweet.17", "Tweet.18", "Tweet.19", "Tweet.20"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Single Line Tweet"),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Single Line Tweet", Splitter.SplitTextByAnyDelimiter(#"Punctuation and Line Breaks", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Single Line Tweet"),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter1", each ([Single Line Tweet] <> "")),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Filtered Rows", {"Single Line Tweet"}, #"Stop Words", {"Stop Word"}, "Stop Words", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true]),
    #"Expanded Stop Words" = Table.ExpandTableColumn(#"Merged Queries", "Stop Words", {"Stop Word"}, {"Stop Word"})
in
    #"Expanded Stop Words"

Date	Tweet	Stock Name	Company Name
2022-09-29 23:41:16+00:00	Mainstream media has done an amazing job at brainwashing people. Today at work, we were asked what companies we believe in ; I said @Tesla because they make the safest cars ; EVERYONE disagreed with me because they heard“they catch on fire ; the batteries cost 20k to replace”	TSLA	Tesla, Inc.
2022-09-29 23:24:43+00:00	Tesla delivery estimates are at around 364k from the analysts. $tsla	TSLA	Tesla, Inc.
2022-09-29 23:18:08+00:00	3/ Even if I include 63.0M unvested RSUs as of 6/30, additional equity needed for the RSUs is 63.0M x $54.20 = $3.4B. If the deal closed tomorrow at $54.20, Elon would need $2.0B for existing shares plus $3.4B for RSUs, so $5.4B new equity. $twtr $tsla	TSLA	Tesla, Inc.
2022-09-29 22:40:07+00:00	@RealDanODowd @WholeMarsBlog @Tesla Hahaha why are you still trying to stop Tesla FSD bro! Get your **bleep** together and make something better? Thats how companies work, they competed. Crying big old ass fart clown!	TSLA	Tesla, Inc.
2022-09-29 22:27:05+00:00	@RealDanODowd @Tesla Stop trying to kill kids, you sad deranged old man	TSLA	Tesla, Inc.
2022-09-29 22:25:53+00:00	@RealDanODowd @Tesla This is you https://t.co/3Ml1XawSEb	TSLA	Tesla, Inc.
2022-09-29 22:24:22+00:00	For years @WholeMarsBlog viciously silenced @Tesla critics. Failing to silence me, he desperately lashes out  with childish insults about me, my company, my products - and even my 💩! His fear and impotence spurs me on to ensure that everyone understands Full Self-Driving is💩. https://t.co/lw84VzwW5U	TSLA	Tesla, Inc.
2022-09-29 22:23:54+00:00	$NIO just because I'm down money doesn't mean this is a bad investment. The whole market, everything sucks right now. 2-5 years from now, I'm confident it will pay off. Long the best $AAPL $AMZN $TSLA $GOOGL $NIO	TSLA	Tesla, Inc.
2022-09-29 22:23:28+00:00	50 likes for some $SPY $TSLA charts to study! ❤️	TSLA	Tesla, Inc.
2022-09-29 22:15:01+00:00	@MrJames__321 @KellyRoofing @TeslaSolar @elonmusk @Tesla The powerwalls themselves are waterproof, but what could be bad is any wiring in the house that could be damaged.	TSLA	Tesla, Inc.
2022-09-29 21:58:00+00:00	Tomorrow, Tesla, $TSLA, AI day 2 is ongoing. Unusual Whales is bringing some Tesla experts to discuss what to expect and $TSLA generally. Join below to prepare before the event at 6:55PM EST: https://t.co/hObh7OdVs5	TSLA	Tesla, Inc.
2022-09-29 21:33:05+00:00	Powerwall FTW! https://t.co/GWAaXXCrYv	TSLA	Tesla, Inc.
2022-09-29 21:11:30+00:00	$TSLAQ: Tesla’s dead in Europe! Everyone else is passing $TSLA in the EU now in EV unit sales! If you don’t believe us, go to eu-evs and see for yourself! Me: <goes to eu-evs> 🧐 https://t.co/GAyiZAKF56	TSLA	Tesla, Inc.
2022-09-29 21:00:25+00:00	The irony is so thick you can cut it with a knife. @Tesla https://t.co/8CihPdpns7	TSLA	Tesla, Inc.
2022-09-29 20:15:41+00:00	Tesla AI day in 24 hours. Are you ready? $TSLA rocks big time. https://t.co/GxtdO2utB4	TSLA	Tesla, Inc.
2022-09-29 19:38:29+00:00	Picked up some $TSLA shares at $269 https://t.co/4UQVchSs2R	TSLA	Tesla, Inc.
2022-09-29 19:36:59+00:00	2/ Even if @elonmusk loses the $TWTR trial (would more likely settle at $51-$52/share), Elon would only have to raise $2-$3B more from $TSLA sales (raised $15B equity) to fund the equity needed to complete the deal, assuming the $7B 3P equity and $13B debt financing still intact. https://t.co/PqtHF2sSmJ	TSLA	Tesla, Inc.
2022-09-29 19:27:54+00:00	Everyone should keep a long term perspective. It’s always darkest before the dawn. With 2026 EPS of $20.80, at 40x P/E (2x PEG), $TSLA worth $832 by 2026. Discounting this back at 13.6% (4.0% 10yrTY, 6% ERP, 1.6x beta), $TSLA value $550. That’s 100% upside over 6-12 mos. https://t.co/oPn2JMXBrh	TSLA	Tesla, Inc.
2022-09-29 19:24:50+00:00	$TSLA Cybertruck can act as a boat according to Elon. https://t.co/aahIkZwUCT	TSLA	Tesla, Inc.

[Garcia16]

Thank you.