Word Frequency Analysis in Power BI (and R)

Подписаться 11 тыс.

Просмотров 27 тыс.

50% 1

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

Опубликовано:

11 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 27

@GTEM19889 2 года назад

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 2 года назад

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

@xPanshishux 3 года назад

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

@LondonBusinessAnalyticsGroup 3 года назад

Glad it was helpful!

@raddleriddle7752 3 года назад

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 3 года назад

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 3 года назад

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

@LondonBusinessAnalyticsGroup 3 года назад

@@stryder1587 use the Unpivot operator.

@four321zero 3 года назад

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 3 года назад

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 3 года назад

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 3 года назад

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 2 года назад

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 4 месяца назад

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 3 года назад

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

@LondonBusinessAnalyticsGroup 3 года назад

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 3 года назад

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

@efficiencydna897 3 года назад

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

@LondonBusinessAnalyticsGroup 3 года назад

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

@efficiencydna897 3 года назад

@@LondonBusinessAnalyticsGroup thank you

@theway19 3 года назад

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 3 года назад

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 3 года назад

@@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 3 года назад

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.