PQ - Letter count in each word of text

[sandy666]

wordslistcnt

The Hunchback of Notre Dame.The Hunchback of Notre Dame.3,9,2,5,4

Letter count in each word of textLetter count in each word of text6,5,2,4,4,2,4

HOW TO ATTACH YOUR SAMPLE WORKBOOKHOW TO ATTACH YOUR SAMPLE WORKBOOK3,2,6,4,6,8



// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Blank = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.Field Values(_), {"", 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