Word Count

[LtVVaughn]

I am trying to find the number of times specific words occur in a column. For instance, I am serching for "visit" and "call" (not case sensitive) within a column. These words may occur more than once in a particular cell of the column and I need to count each time these words occur, not a count per word, but count the times both words occur in cells in the column.

Thanks in advance!

 

[sandy666]

just for fun

Source		1st				2nd
Raw		call	visit	total		call	visit	total
Call me before your visit don’t visit me before call Me		5	4	9		5	4	9
Visit visit call call call

//1st
let
    Source = Excel.CurrentWorkbook(){[Name="Table21"]}[Content],
    Lower = Table.TransformColumns(Source,{{"Raw", Text.Lower, type text}}),
    Split = Table.ExpandListColumn(Table.TransformColumns(Lower, {{"Raw", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Raw"),
    Contain = Table.SelectRows(Split, each Text.Contains([Raw], "visit") or Text.Contains([Raw], "call")),
    Count = Table.Group(Contain, {"Raw"}, {{"Count", each Table.RowCount(_), type number}}),
    Pivot = Table.Pivot(Count, List.Distinct(Count[Raw]), "Raw", "Count", List.Sum),
    Total = Table.AddColumn(Pivot, "total", each [call]+[visit])
in
    Total

//2nd
let
    Source = Excel.CurrentWorkbook(){[Name="Table21"]}[Content],
    Lower = Table.ToColumns(Table.TransformColumns(Source,{{"Raw", Text.Lower, type text}})),
    C2T = Table.FromList(Lower, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Ren = Table.RenameColumns(C2T,{{"Column1", "Raw"}}),
    Extract = Table.TransformColumns(Ren, {"Raw", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    call = Table.AddColumn(Extract, "call", each List.Count(Text.Split([Raw],"call"))-1),
    visit = Table.AddColumn(call, "visit", each List.Count(Text.Split([Raw],"visit"))-1),
    total = Table.AddColumn(visit, "total", each [call]+[visit]),
    TSC = Table.SelectColumns(total,{"call", "visit", "total"})
in
    TSC