PQ - Distinct keywords from text

Keywords		Text
cat		The dog chased the cat.
dog		The cat ate the bird.
bird		The cat is a cat.
		The dog, bird, and cat.
		consumer watchdog
		catwoman and bird
		Dog and Bird

Code:

---

let
    Keywords = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Text = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Index = Table.AddIndexColumn(Text, "Index", 1, 1),
    Split = Table.ExpandListColumn(Table.TransformColumns(Index, {{"Text", Splitter.SplitTextByAnyDelimiter({" ",",","."}, QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Text"),
    Join = Table.AddColumn(Split, "Custom", each Keywords),
    Expand = Table.ExpandTableColumn(Join, "Custom", {"Keywords"}, {"Keywords"}),
    IF = Table.AddColumn(Expand, "Custom", each if Text.Contains([Text], [Keywords]) then [Keywords] else null),
    Group = Table.Group(IF, {"Index"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "List", each List.Distinct([Count][Custom])),
    Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    TSC = Table.SelectColumns(Extract,{"List"})
in
    TSC

---

Case sensitive and words contained keywords

List

dog, cat

cat, bird

cat

dog, bird, cat

dog

cat, bird

Code:

---

IF = Table.AddColumn(Expand, "Custom", each if Text.Contains([Text], [Keywords], Comparer.OrdinalIgnoreCase) then [Keywords] else null),

---

Case insenstive and words contained keywords

List

dog, cat

cat, bird

cat

dog, bird, cat

dog

cat, bird

dog, bird

Code:

---

IF = Table.AddColumn(Expand, "Custom", each if Text.Contains([Text], [Keywords], Comparer.OrdinalIgnoreCase) and Text.Length([Text]) = Text.Length([Keywords]) then [Keywords] else null),

---

Case insensitive without words contained keywords

List

dog, cat

cat, bird

cat

dog, bird, cat

bird

dog, bird

Code:

---

IF = Table.AddColumn(Expand, "Custom", each if Text.Contains([Text], [Keywords]) and Text.Length([Text]) = Text.Length([Keywords]) then [Keywords] else null),

---

Case sensitive without words contained keywords

List

dog, cat

cat, bird

cat

dog, bird, cat

bird