Forum Discussion

TheOriginalDeeb's avatar
TheOriginalDeeb
Frequent Visitor
8 years ago
Solved

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...
  • RobertSlattery's avatar
    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