Word Frequency Analysis in Power BI (and R)

We analyse respondents’ comments of a recent survey and a Victorian horror novel. We use Power BI’s Query Editor to split the text into words. One stage of the process is to remove punctuation - this requires several steps in M but we add an R step to do this elegantly and simply.
The datasets and course materials used in the examples are at github.com/MarkWilcock/lbag-o...
Details and signup for events at www.meetup.com/London-Busines....
Music: bensound.com

[@GTEM19889]

Struggling to follow the step where you "split into words"... you mention you will come back to it but watched the whole video and I cannot work out how you did that from this video.

[@vle987]

Under "Split Column" option there is an advanced option to split into rows using a delimiter

[@xPanshishux]

Excelent ! I've learnt a lot with this video ! Thank you so much, greetings from Chile.

[@LondonBusinessAnalyticsGroup]

Glad it was helpful!

[@raddleriddle7752]

Hi great vid! But how did you get from text all in one cell to it being one word per cell? It’s like there is a step missing. Please help as this is what I really need

[@LondonBusinessAnalyticsGroup]

Hi James, @@raddleriddle7752 I used the Split Column operation in the Power BI Query Editor. Each video has a link to an example Power BI Desktop file that you can download and see all the steps

[@stryder1587]

@@LondonBusinessAnalyticsGroup After splitting by delimited space, I have many columns, how do you combine them all into 1 column?

[@LondonBusinessAnalyticsGroup]

@@stryder1587 use the Unpivot operator.

[@four321zero]

This is exactly what I wanted. Best video on the topic. Easiest to follow. Only thing I wish was that you could explain what a 'measure' is and what does adding the one you did, mean. Anyway, i blindly followed instruction and got my job done :D

[@LondonBusinessAnalyticsGroup]

Hi four321zero, A measure is just a fancy name for a number that we add up - such as the count of words. Glad you enjoyed the video. - Mark

[@franciscorossier5239]

Thanks for this video, it was a great help, and the documentation is perfect, downloading the example file, I learned a lot. The step using the scrip in R is still a bit of a mistery to me, but I guess I'll figure it out. Do I need to download/copy paste that script into R from some place before using it? Thanks and greetings from Chile.

[@jasonq4254]

Very excellent video, thank you! I got one question for you, how do you handle with plural form words when calculate word frequency? Maybe I miss something in your process?

[@mikeweber6565]

stop words I think. ie. ', s, t etc.

[@maleitne]

Very nice. Is there also a possibility to keep the survey IDs zu filter the surve by the word?

[@sidh1973]

Excellent video, I learnt a lot! Thank you! What would be the advantage of building a WordCloud visual in PowerBI as against getting the WordCloud survey analysis from Microsoft Forms survey besides the flexibility of filtering?

[@misslyceum]

Thanks for a great video! Is there anyway to change plural words to singular, such as "servants" and "servant" are counted as one?

[@LondonBusinessAnalyticsGroup]

HI Tra, You could always remove the final s! You can do this in a R step using a R library like tidytext and a technique called stemming. - Mark

[@misslyceum]

@@LondonBusinessAnalyticsGroup thank you for your reply. Really helpful :)

[@efficiencydna897]

thanks, great video. “split the text into words”，if my sentences are chinese such as “我今天吃了很多饭”,if there any method to do this?

[@LondonBusinessAnalyticsGroup]

Hi EfficiencyDNA, do Chinese characters have a word separator? If so, use that. - Mark

[@efficiencydna897]

@@LondonBusinessAnalyticsGroup thank you

[@theway19]

How did you create word table? is it done manually? I have like 12000 rows in excel with comments, how am I going to create a word list out of that, please suggest.

[@LondonBusinessAnalyticsGroup]

HI theway19, I used R script in a Query Editor script to split sentences into words, then used the Query Editor Group By to count words. It's all in the video. - Mark

[@LadyCrimson10]

@@LondonBusinessAnalyticsGroup I watched the video but don't actually see the exact script that was used to split the sentence into words and distribute them in rows and not across columns.

[@LondonBusinessAnalyticsGroup]

Hi @@LadyCrimson10 , I used a "split column by delimiter" step. There is an advanced option to split into rows. Expand the advanced options section to see it.

[@markcarmicunningham]

@@LondonBusinessAnalyticsGroup - "There is an advanced option to split into rows"... a nugget of pure gold

[@vipul4raf]

How did you created Stop_word table you have not mentioned. I think you have skiped some timline.