Forum Discussion
Word Cloud - Getting Top N Word Frequency
Hello all,
I have a table with multiple columns. One of the columns is "action taken." Each row of the column is a sentence. My task is to identify which top words in the actions taken column have been used the most number of words.
I am using a word cloud to visualize this. The word cloud gives me an additional feature to use stop words which are essential for my task. The word cloud also gives me the flexibility to filter based on slicers, which are also very necessary for my purpose.
The problem is that I only want to visualize words that have the top N (let's say, 50) frequency in the word cloud. I could not find any option to do this.
Can anyone help me out to filter out the top 50 most occurring words in the column? The only restriction is that I should be able to apply slicers and stop words ( or any equivalent of stop words).
I have tried copying the column as a separate query in PowerQuery and making it a list of words, but that does not help as I am unable to use the slicers based on other columns on the new word cloud.
Hi Anonymous
New a blank query and paste above formula in advanced editor.Then you can check the steps in query editor.Attached sample file.
Regards,
3 Replies
v-cherch-msftMicrosoft Employee
Hi Anonymous
You may use Split,Unpivot columns and group by function to get that.For example:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY6xDsIwDAV/xcpctRQGBgYkNiQW5qpDSNzKUhOX2OH7SQtDEfvdvdd1pm12bbPftUdTmRsnDECz5ACeJ04gpGADagWOo6BT1JzAeppJHMURcCKtTV/9hq5qnxkXR5MFlOJbB54cKclpxfcb/IJWs5SxB8GL/LqBIgiBE2GUVThshFj4ZAPYYSifytuVnlmWv5/a38i3VcM9M9BIpVABxWem8i6w57Pp+zc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Text = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Text", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Text", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Text.1", "Text.2", "Text.3", "Text.4", "Text.5", "Text.6", "Text.7", "Text.8"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Text.1", type text}, {"Text.2", type text}, {"Text.3", type text}, {"Text.4", type text}, {"Text.5", type text}, {"Text.6", type text}, {"Text.7", type text}, {"Text.8", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Date"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"Value"}, {{"Count", each Table.RowCount(_), type number}}), #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Descending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Rank_TopN", 1, 1) in #"Added Index"Regards,
- v-cherch-msft
Hi Anonymous
New a blank query and paste above formula in advanced editor.Then you can check the steps in query editor.Attached sample file.
Regards,