Split word into Delimiter not functioning properly

[Anonymous]

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. 

 

 

[AlexisOlson · Accepted solution]

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"

[parry2k]

@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.⚡