Forum Discussion
Word cloud multi-select intersection (AND instead of OR)
- 8 years ago
I used a Splitter function Splitter.SplitTextByWhitespace() to add a column splitting each sentence into words. Then I expanded this column into new rows.
Here is the query used in my dropbox link.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKslIVUhOLFEoKs0rVorVgQik5KejCuTmlxanIoRyK1FEYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Sentence = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sentence", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Sentence", "Sentence - Copy"), #"Lowercased Text" = Table.TransformColumns(#"Duplicated Column",{{"Sentence - Copy", Text.Lower, type text}}), #"Added Custom" = Table.AddColumn(#"Lowercased Text", "Word", each Splitter.SplitTextByWhitespace([#"Sentence - Copy"])), #"Expanded Word" = Table.ExpandListColumn(#"Added Custom", "Word"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Word",{{"Word", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Sentence - Copy"}) in #"Removed Columns"With real text data, I imagine you would need to clean up the text in various ways and remove punctuation.
I realise this is an old topic, but it is almost exactly what I am looking for as well.
I have a large dataset with work hours logging with descriptions for each entry. I have then used an external algorithm to extract keywords, which are then added in a comma sepparated list, see the table below. I have a word cloud visual with Keywords as the category and Hours as the values.
Now, the problem I have with the proposed solution is that this would blow up my dataset, considering the average number of keywords is 6/7. Would anyone know a clever way of solving this without having a lot of duplicate lines? I thought of using the FIND() function, but my problem is that the ALLSELECTED() function will return [Updating, Requirements, Document] instead of just Updating when we select that word.
| Date | Project | Hours | Description | Keywords |
| 20-07-2020 | Project X | 2.5 | Updating the Requirements Document | [Updating, Requirements, Document] |