Forum Discussion
Split word into Delimiter not functioning properly
Hello,
I am trying to analyze suvey data into PowerBI. I created Powerapps with Rich Field text editor to receive the feedback. What I am trying to accomplish is to split the text into words to generate word frequency count, and a word cloud. I was using split column in delimiter function to split the words, but when text is entered like below BI doesn't split the text in delimiter. Is there anyway I can achieve spliting text into words.
What delimiter are you splitting on? It looks like you might be splitting on space " " rather than a line feed.
If you use the GUI to split your text column, it will produce a step that looks like this:
= Table.ExpandListColumn( Table.TransformColumns( Source, {{"Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType} }}), "Column1")You can ignore most of that. Just focus on the Splitter part. Replace this
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv)with this
Splitter.SplitTextByAnyDelimiter({" ", "#(lf)"}, QuoteStyle.Csv)if you want to split on spaces and line feeds.
Here's a full sample M query you can paste into the Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLjHVi8mLyUNnKcXqRCu55pWkFikU5+emKhRlJmcolKRWlChkpBalQqTT8ov0kABIc0lmbqpSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByAnyDelimiter({" ", "#(lf)"}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1") in #"Split Column by Delimiter"
2 Replies
What delimiter are you splitting on? It looks like you might be splitting on space " " rather than a line feed.
If you use the GUI to split your text column, it will produce a step that looks like this:
= Table.ExpandListColumn( Table.TransformColumns( Source, {{"Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType} }}), "Column1")You can ignore most of that. Just focus on the Splitter part. Replace this
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv)with this
Splitter.SplitTextByAnyDelimiter({" ", "#(lf)"}, QuoteStyle.Csv)if you want to split on spaces and line feeds.
Here's a full sample M query you can paste into the Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktLjHVi8mLyUNnKcXqRCu55pWkFikU5+emKhRlJmcolKRWlChkpBalQqTT8ov0kABIc0lmbqpSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByAnyDelimiter({" ", "#(lf)"}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1") in #"Split Column by Delimiter"Anonymous how does the raw data looks like, not fully clear from your post.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
