Forum Discussion
TheOriginalDeeb
8 years agoFrequent Visitor
Is Word Co-Occurence possible in Power Query?
I'm hoping to re-create a solution that was achieved using an R script (because R makes no sense to me) : https://stackoverflow.com/questions/27153320/build-word-co-occurence-edge-list-in-r Here's...
- 8 years ago
Here is a solution that is in one query in M.
let Source = DF, sentences = List.Transform(DF[text], each Text.Lower(_)), minSentences = List.Transform(sentences, each List.Distinct(Text.Split(_, " ")) ), wordsList = List.Combine(minSentences), wordsDistinct = List.Distinct(wordsList), #"Converted to Table" = Table.FromList(wordsDistinct, Splitter.SplitByNothing(), {"word"}, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"word", type text}}), crossTab = List.Accumulate(wordsDistinct, #"Changed Type", (table, col) => Table.AddColumn(table, col, each 0, Int64.Type) ), records = Table.TransformRows(crossTab, each let currentWord = [word], currentWordIndex = List.PositionOf(wordsDistinct, currentWord) in // input the current row into an accumulator that iterates over the sentences List.Accumulate(minSentences, _, (r, s) => // if the current word is in the sentence if List.Contains(s, currentWord) then let coWords = List.RemoveMatchingItems(s, {currentWord}) in // increment co-word columns if not List.IsEmpty(coWords) then // iterate the co-words operating on the row to increment the column List.Accumulate(coWords, r, (row, coWord) => if List.PositionOf(wordsDistinct, coWord) > currentWordIndex then Record.TransformFields(row, {coWord, each _ + 1}) else row ) else r else r ) ), crossTabPopulated = Table.FromRecords(records), #"Changed Type1" = Table.TransformColumnTypes(crossTabPopulated,{{"word", type text}, {"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}, {"d", Int64.Type}, {"e", Int64.Type}}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type1", wordsDistinct, "coWord", "Count"), output = Table.SelectRows(#"Unpivoted Only Selected Columns", each [word] <> [coWord] and [Count] <> 0) in output
v-yulgu-msft
Microsoft Employee
8 years agoHi TheOriginalDeeb,
After loading original table into desktop, first duplicate it in Query Editor mode.
1. Split column [text].
2. You need to convert above table into below structure via Power Query (select columns one by one and union). Unfortunately, I haven't worked it out as I am not very familiar with M code. And remove duplicate values.
3. Remove unnecessary columns. Sort [Text] in Ascending order. Then, add an index column.
4. Apply all above changes. Switch to report view mode. You then need to create several auxilliary tables like below:
Test Table3 =
FILTER (
CROSSJOIN (
SELECTCOLUMNS (
'Test Table2',
"Word1", 'Test Table2'[Text],
"Index1", 'Test Table2'[Index]
),
SELECTCOLUMNS (
'Test Table2',
"Word2", 'Test Table2'[Text],
"Index2", 'Test Table2'[Index]
)
),
[Index1] < [Index2]
)
test Table4 =
ADDCOLUMNS (
CROSSJOIN (
'Test Table',
SELECTCOLUMNS (
'Test Table3',
"Word1", 'Test Table3'[Word1],
"Word2", 'Test Table3'[Word2]
)
),
"Find Word1", AND (
NOT ( ISERROR ( FIND ( [Word1], 'Test Table'[text] ) ) ),
NOT ( ISERROR ( FIND ( [Word2], 'Test Table'[text] ) ) )
)
)
Test Table5 =
SUMMARIZE (
'test Table4',
'test Table4'[Word1],
'test Table4'[Word2],
"freq", CALCULATE (
COUNTROWS ( 'test Table4' ),
FILTER ( 'test Table4', 'test Table4'[Find Word1] = TRUE () )
)
)
Best regards,
Yuliana Gu