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

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!

"...", "!!!!" are multi-character strings. "." or "!" are single character

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.

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.

Error with Text.Trim | Microsoft Fabric Community

16 Replies

HotChilli
Community Champion
3 years ago
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

Community Champion

3 years ago

We'll need to see some sample data +

#"Punctuation and Line Breaks"

list

DemoFour

Continued Contributor

3 years ago

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
Advocate II
3 years ago
Thank you.

HotChilli
Community Champion
3 years ago
Right, so the list needs to hold single text values. It has lots of multi-character values
- DemoFour
  Continued Contributor
  3 years ago
  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.
  - HotChilli
    Community Champion
    3 years ago
    "...", "!!!!"
    
    are multi-character strings.
    
    "." or "!" are single character

Forum Discussion

Error with Text.Trim

16 Replies

Related content

Text.Trim over entire table

error message

network error

RDL Error

Underlying Error: OnPremiseServiceException