Splicing together text string "pairs" from adjacent columns

[paul_cranberry]

I have data that looks like this...

Name	Colors	Fruit
Bob	Yellow; Red; Green; Blue	Banana; Apple; Grape; Blueberry
Karen	Green; Blue; Brown	Grape; Blueberry; Kiwi

How can I get a new column that concatenates text strings from "Colors" with the matching text string in "Fruit"? For example...

Name	Matched Pairs
Bob	Yellow - Banana; Red - Apple; Green - Grape; Blue - Blueberry
Karen	Green - Grape; Blue - Blueberry; Brown - Kiwi

Note: The number of text strings in "Colors" is always equal to the number of text strings in its adjacent "Fruit" cell.

What I've tried:

I tried adding an index column and then splitting "Colors" and "Fruit" by delimiter (to rows), but I get a huge number of new rows and duplicate information that I don't know how to work my way out of...

Index	Name	Colors	Fruit
0	Bob	Yellow	Banana
0	Bob	Yellow	Apple
0	Bob	Yellow	Grape
0	Bob	Yellow	Blueberry
0	Bob	Red	Banana
0	Bob	Red	Apple
0	Bob	Red	Grape
0	Bob	Red	Blueberry
etc.

[OwenAuger · Accepted solution]

HI @paul_cranberry 

Here's one way of handling it.

Requires writing a little M code rather than pure interface.

Is this the sort of thing you're looking for?

let
  Source = #table(
    type table [Name = text, Colors = text, Fruit = text],
    {
      {"Bob", "Yellow; Red; Green; Blue", "Banana; Apple; Grape; Blueberry"},
      {"Karen", "Green; Blue; Brown", "Grape; Blueberry; Kiwi"}
    }
  ),
  #"Split Text into Lists" = Table.TransformColumns(
    Source,
    {
      {"Colors", Splitter.SplitTextByDelimiter("; "), type {text}},
      {"Fruit", Splitter.SplitTextByDelimiter("; "), type {text}}
    }
  ),
  #"Zip Lists" = Table.CombineColumns(
    #"Split Text into Lists",
    {"Colors", "Fruit"},
    each List.Zip({_{0}, _{1}}),
    "Matched Pairs"
  ),
  #"Combine Each Pair Hyphen" = Table.TransformColumns(
    #"Zip Lists",
    {{"Matched Pairs", each List.Transform(_, Combiner.CombineTextByDelimiter(" - "))}}
  ),
  #"Combine Pairs Semicolon" = Table.TransformColumns(
    #"Combine Each Pair Hyphen",
    {{"Matched Pairs", Combiner.CombineTextByDelimiter("; ")}}
  )
in
  #"Combine Pairs Semicolon"