words list cnt
The Hunchback of Notre Dame. The Hunchback of Notre Dame. 3,9,2,5,4
Letter count in each word of text Letter count in each word of text 6,5,2,4,4,2,4
HOW TO ATTACH YOUR SAMPLE WORKBOOK HOW TO ATTACH YOUR SAMPLE WORKBOOK 3,2,6,4,6,8


Code:
// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Blank = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    Idx = Table.AddIndexColumn(Blank, "Index", 1, 1),
    Split = Table.ExpandListColumn(Table.TransformColumns(Idx, {{"words", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "words"),
    Count = Table.AddColumn(Split, "Count", each Text.Length(Text.Select([words], {"A".."Z","a".."z"})), type number),
    Grp = Table.Group(Count, {"Index"}, {{"Count", each _, type table}}),
    list = Table.TransformColumns(Table.AddColumn(Grp, "list", each [Count][words]), {"list", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    cnt = Table.TransformColumns(Table.AddColumn(list, "cnt", each [Count][Count]), {"cnt", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    TSC = Table.SelectColumns(cnt,{"list", "cnt"})
in
    TSC