Forum Discussion

Mouseman85's avatar
Mouseman85
Icon for Helper I rankHelper I
5 years ago

Return a top 5 result from multiple excel columns containing text

Hi,

 

I'm looking for a way to return the top 5 most used words from a range of columns in an excel file. What i need to do is look through the 5 columns and total up how many times the words are shown. For example: 

 

The word Pet Friendly is used in each column how many times is it used, etc i then need to return the amounts so I can rank them in an order of highest to lowest. I've attached an image showing the table data, the words are always the same in each column they are just in rows as they each relate to a city, but i need an overal of all the words. Is that possible?


Thanks in advance

 

 

Need Help
Tips and Tricks

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  Mouseman85 ,

    Here are the steps you can follow：

    1. Open the power query, select the five columns of data, select Transform-Unpoivot Columns-Unpoivot Only Selected Columns

    Result:

    2. Create measure.

    count = CALCULATE(COUNT('Table'[Value]),FILTER(ALL('Table'),'Table'[Value]=MAX('Table'[Value])))
    rank_5 =
var _rank=RANKX(ALL('Table'),'Table'[count],,DESC,Dense)
return
IF(_rank<=5,_rank,BLANK())

    Result：

    You can downloaded PBIX file from here.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Mouseman85's avatar
      Mouseman85
      Icon for Helper I rankHelper I
      5 years ago

      Thank you so much for this Liu Yang,

       

      One thing i have to ask is those columns are part of a wider set of data, i tried the tutorial below but by unpiviting the columns its screwed up all the other data as it then obviously makes 5 copies and duplicates some data, am i better off create a seperate table for the amenties to sit in? If i do that how do i link that table to the other one so that when they select a region it pulls the right line through below is an example of the excel file, i need them touse a slicer to pick which Market they want to see the results from, does that make sense?