Reply
Member
Posts: 47
Registered: ‎03-11-2018

frequency of words in our customer queries, looking for trends/patterns ( not with a word cloud )

[ Edited ]

Hello,

 

We looking for frequency of words is our customer queries, looking for trends/patterns.  The obvious visualisation to use world be Word Cloud.

This is helpful but I don’t what to have to drag my mouse over the word to see how many time the word appears.

 

We looking for frequency of words is our customer queries, looking for trends/patterns

 

Ideally, I would like to be able to view the top 10 to 20  words that are appearing in my data. Something like this:

 

100 Scale

99 Printed

50 Maps

30 Downloading

20 Geology

20 Digital

20 Grid

19 Issue

10 Topographic

5 Problem

 

I have looked for other examples and they get pointed to here: Power BI, Text Analytics and the United States Congress

 https://social.technet.microsoft.com/wiki/contents/articles/31461.power-bi-text-analytics-and-the-un...

 

I can do some very simple coding with Power BIs scripting this is a little complex for me to replicate/use.

 

Can anyone point me in a direction of visualisation that might suit my needs or some simpler coding to explore?

Established Member
Posts: 203
Registered: ‎07-12-2017

Re: frequency of words is our customer queries, looking for trends/patterns ( not with a word cloud

Hi @steambucky,

 

Can you share a sample of your data from which you need this output.

 

Regards

Affan

Member
Posts: 47
Registered: ‎03-11-2018

Re: frequency of words is our customer queries, looking for trends/patterns ( not with a word cloud

Hi Affan, 

 

I coulnd see a way to upload a CSV so this is the type of data we would be looking at 

 

--

test data 1

--

Phone call  advised he has inadvertantly ordered the same product twice as he deleted the wrong plan from the payment cart as it had been held over from his purchase yesterday.  He would like a refund. He also expressed that he would like other searches to be made available eg Historical titles and plans etc

--

test data 2

--

Hi,  As explained by phone, a title ordered and paid for in a previous session was  still in My Cart when I ordered a new title search today. This happens almost every time I order a new title and so I have to continually remove the title that I have previously paid for from My Cart, which is what I meant to do today. However I have received and have been re-billed  for the previously ordered title, see the title for today’s Receipt number  is the same title ordered for Receipt No  on Is it possible for this to be refunded as I should really not have to be removing items from My Cart that I have already paid for.
Community Support Team
Posts: 7,918
Registered: ‎05-02-2017

Re: frequency of words is our customer queries, looking for trends/patterns ( not with a word cloud

Hi @steambucky,

 

You can upload it to the cloud drive like OneDrive then share the download link here. Please mask the sensitive parts.

The key point here is how to transform the original data into a reasonable format. Please refer to the demo below.

1. If all the words are columns, we can transpose it. 

frequency-of-words-is-our-customer-queries

2. Create a measure like below.

Measure =
VAR ranks =
    RANKX ( ALL ( word[Column1] ), CALCULATE ( COUNT ( word[Column1] ) ) )
RETURN
    IF ( ranks <= 3, ranks, BLANK () )

frequency-of-words-is-our-customer-queries2

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Community Support Team
Posts: 7,918
Registered: ‎05-02-2017

Re: frequency of words in our customer queries, looking for trends/patterns ( not with a word cloud

Hi @steambucky,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 47
Registered: ‎03-11-2018

Re: frequency of words is our customer queries, looking for trends/patterns ( not with a word cloud

Apologies for late reply -  i have been on extended sick leave. 

 

I have implemeneted the code you have suggest and it isnt creating the desired result. My desired result is a list of words and count of the times they apear in the data 

 

eg: 

 

5 Ordered

7 Title

4 Cart 

etc 

 

Is my request actually beyond the scope of calculated columns, and what i actually need is spec

 

You  can find my files here: One Drive:  

 

 

 

 

Word_frequency.png

Established Member
Posts: 203
Registered: ‎07-12-2017

Re: frequency of words is our customer queries, looking for trends/patterns ( not with a word cloud

@steambucky

 

Please check if the solution in this post can provide you some way forward.