Grouping of Questions to Create Category

[rkee10]

Is it possible to create a report from a list of questions wherein it will group the type of questions as categories?  For example, i have these questions:

- What is a guitar

- Guitar

- define guitar

- How to cook meat?

3 counts should be under GUITAR query or category and 1 count would be under Cooking.  I hope this is clear.  Let me know otherwise.  Thanks!

[rkee10]

Yes, the output should give a category and its count. 

[qqqqqwwwweeerrr · Accepted solution]

Hi @rkee10 

If my understanding is correct then here are the step, to achieve what you have you need to create two custom column

First column

NewColumn =

VAR Sentence = 'Table'[Sentence]

VAR WordList =

SELECTCOLUMNS(

UNION(

ROW("Word", "guitar"),

ROW("Word", "cook")

-- Add more words here as needed

),

"Word", [Word]

)

VAR FoundWords =

FILTER(

WordList,

CONTAINSSTRING(Sentence, [Word])

)

RETURN

CONCATENATEX(FoundWords, [Word], ", ")

Second column:

CountColumn =

CALCULATE(

    COUNTROWS('Table'),

    FILTER('Table', 'Table'[NewColumn] = EARLIER('Table'[NewColumn]))

)

Here is below output for the same

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

Regards

[rkee10]

Thanks for this one, @qqqqqwwwweeerrr !  By the way, how can I put this count to guitar:

- How can I play an instrument (or 6 string instrument)?

I would like to count this under guitar.  So for the answer you provided, the guitar should be 4 if we include this new sentence.  Basically, I want to count words in one category who have the same meaning (synonym).  Let me know if this is clear.  Appreciate your effort on this one.

[qqqqqwwwweeerrr]

Hi @rkee10 

What is expected output say you want to create a category column, based on given key words?

[rkee10]

Yup, based on given keywords.