Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Dom123
New Member

How to summarize/count the frequency of keywords from 3 different columns?


Ok, this seemed so simple to do but it doesn't seem as simple to implement. 🙂

How to visualize the overall count of each keyword when they are coming from 3 different columns?

 

Simplified example of the table (only showing data to be aggregated, other columns not shown):

Name   Key1  Key2  Key3
Item1ACnull
Item2Anullnull
Item3CBC
Item4DDnull
Item5DED
Item6Enullnull

 

Explanation:

  • I have a list of items with associated keywords
    • The table/list has many other columns so "unpivot" is not feasible.
    • Each item can have up to 3 keywords (each in a different column named Key1, Key2, Key3).
    • Each item has at least 1 keyword (in Key1).
    • When an item has only 1 or 2 keywords, there is "null" value in the 2nd or 3rd column respectively.
    • Not all types of keywords necessarily appear in column Key1 so it cannot be used as a "reference".
    • Keywords are not necessarily known so a specific text search is not possible.

The desired results should be:

 

Keyword   Frequency
A2
B1
C3
D4
E2

 

What I tried and didn't work:

  • I tried creating a new table of "unique" keywords from the three columns with functions DISTINCT, UNION, and FILTER but I end up with a table that can only have a relationship with one column so the calculations are all wrong when adding the 2nd and 3rd column since it uses the first column as reference but that first column does not necessarily includes all possible keywords.
  • I tried creating a new table with SELECTCOLUMNS to have only the above information but I still cannot unpivot because this function is only available in Power Query (which refers to the original source data and not that new table).

 

Any information would be greatly appreciated.

 

Thanks!

 

 

 

1 ACCEPTED SOLUTION
vicky_
Super User
Super User

I don't think this is the most performant solution, but I hope it helps:

First I used 

keywords = DISTINCT(
    UNION(
        DISTINCT('Table (3)'[Key1  ]), 
        DISTINCT('Table (3)'[Key2  ]), 
        DISTINCT('Table (3)'[Key3])
    )
)

to create a new table with all of the unique keywords.

 

Then I used a DAX measure: 

frequency = 
var allKeys = UNION(
    SELECTCOLUMNS('Table (3)', "key", 'Table (3)'[Key1  ]),
    SELECTCOLUMNS('Table (3)', "key", 'Table (3)'[Key2  ]),
    SELECTCOLUMNS('Table (3)', "key", 'Table (3)'[Key3])
)
var frequencies = COUNTROWS(FILTER(allKeys, [key] = SELECTEDVALUE(keywords[Key1  ])))
return frequencies

to just get a list of all the keys, and then count them if they matched the value in the unique keywords from the table above. 

 

vicky__0-1691620106937.png

And as you can see, I can get the frequency. I'm sure you can add a filter to remove the null row if that's something that shouldn't appear. 

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

Why is unpivot not feasible?  Select all columns other than the 3 key columns, right click and select "Unpivot Other Columns".


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks but that is messing up all my table (which contains 30 columns of data).

All items/records then becomes duplicates (even triplicates) and that is interfering with all the other types of analysis I am doing.

vicky_
Super User
Super User

I don't think this is the most performant solution, but I hope it helps:

First I used 

keywords = DISTINCT(
    UNION(
        DISTINCT('Table (3)'[Key1  ]), 
        DISTINCT('Table (3)'[Key2  ]), 
        DISTINCT('Table (3)'[Key3])
    )
)

to create a new table with all of the unique keywords.

 

Then I used a DAX measure: 

frequency = 
var allKeys = UNION(
    SELECTCOLUMNS('Table (3)', "key", 'Table (3)'[Key1  ]),
    SELECTCOLUMNS('Table (3)', "key", 'Table (3)'[Key2  ]),
    SELECTCOLUMNS('Table (3)', "key", 'Table (3)'[Key3])
)
var frequencies = COUNTROWS(FILTER(allKeys, [key] = SELECTEDVALUE(keywords[Key1  ])))
return frequencies

to just get a list of all the keys, and then count them if they matched the value in the unique keywords from the table above. 

 

vicky__0-1691620106937.png

And as you can see, I can get the frequency. I'm sure you can add a filter to remove the null row if that's something that shouldn't appear. 

Thank you @vicky_ !

 

It works perfectly! 👍

 

I didn't realize you could actually do that with UNION. When I tried it directly (without a variable), it was removing duplicates (which I wanted to keep of course).

 

FYI, I didn't have the "null" problem because when I created my "unique" list of keywords, I already filtered out null/blank values like this:

keywords = DISTINCT(
    UNION(
        DISTINCT(FILTER(VALUES(Table[Key1],Table[Key1]<>BLANK())), 
        DISTINCT(FILTER(VALUES(Table[Key2],Table[Key2]<>BLANK())), 
        DISTINCT(FILTER(VALUES(Table[Key3]),Table[Key3]<>BLANK()))
    )
)

 

Thank you again, much appreciated!

Dom

 

Nice! I'm happy to hear that worked for you.

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.