Owen Price Power Query Challenge Feb 12 2024 - Brian Julius Solution

gistfile1.txt

let
    String ="Amidst the ancient oak's shelter, the forest floor cradled fallen leaves. Leaves of gold, crimson, and russet danced in the autumn breeze. Breeze whispered secrets to the trees, and the trees listened, their branches swaying in rhythm. Rhythm of life echoed through the woodland—a symphony of rustling leaves and birdcalls. Birdcalls harmonized with the babbling brook, where water flowed over moss-covered stones. Stones, worn smooth by time, held stories of ages past. Past and present intertwined, as sunbeams filtered through the canopy, dappling the earth. Earth embraced memories, and memories lingered—a tapestry woven by seasons. Beneath the ancient oak, a squirrel gathered acorns, preparing for winter.",
    Source = Web.BrowserContents("https://gist.github.com/ncalm/889ed38d780d653c4cce3d0df7f3d031"),
    ExtractHTML = Html.Table(Source, {{"Column1", "TABLE.highlight.tab-size.js-file-line-container.js-code-nav-container.js-tagsearch-file > * > TR > :nth-child(1)"}, {"Column2", "TABLE.highlight.tab-size.js-file-line-container.js-code-nav-container.js-tagsearch-file > * > TR > :nth-child(2)"}}, [RowSelector="TABLE.highlight.tab-size.js-file-line-container.js-code-nav-container.js-tagsearch-file > * > TR"]),
    ProcessStopWords = Table.AddColumn(ExtractHTML, "StopWords", each [
        a = ExtractHTML[Column2],
        b = List.Transform( a, each Text.Remove( _, {"[", "]", ",", ".", """", "'"})),
        c = List.Transform( b, each Text.Lower( Text.Trim(_) ))
    ][c]),
    AddStopWords = ProcessStopWords{0}[StopWords],
    ToTable = Table.FromList(AddStopWords, Splitter.SplitByNothing(), {"StopWords"}, null, ExtraValues.Error),
    AddStringWords = Table.AddColumn(ToTable, "StringWords", each [ 
        x = { Text.Lower( String ) },
        a = Text.ToList( Text.Lower( String )),
        y = List.Transform( a, each if List.Contains({"a".."z"}, _) then _ else " "),
        b = Text.Combine( y, ""),
        c = Text.Split( b, " "),
        d = List.Select(c, each Text.Length(_) > 0),
        e = List.RemoveMatchingItems( d, ToTable[StopWords])
    ][e]),
    StringWords = AddStringWords{0}[StringWords],
    CleanedWords = Table.FromList(StringWords, Splitter.SplitByNothing(), {"Word"}, null, ExtraValues.Error),
    Group = Table.Group(CleanedWords, {"Word"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    FilteredOnes = Table.SelectRows(Group, each ([Count] <> 1)),
    Sort = Table.Sort(FilteredOnes,{{"Count", Order.Descending}, {"Word", Order.Ascending}})
in
    Sort

Original post with problem definition can be found at:
https://www.linkedin.com/posts/owenhprice_excel-data-analytics-activity-7162928568920920064-8JMl?utm_source=share&utm_medium=member_desktop