Splicing together text string "pairs" from adjacent columns

Question

I have data that looks like this...NameColorsFruitBobYellow; Red; Green; BlueBanana; Apple; Grape; BlueberryKarenGreen; Blue; BrownGrape; Blueberry; Kiwi&nbsp;How can I get a new column that concatenates text strings from "Colors" with the matching text string in "Fruit"? For example...NameMatched PairsBobYellow - Banana; Red - Apple; Green - Grape; Blue - BlueberryKarenGreen - Grape; Blue - Blueberry; Brown - Kiwi&nbsp;Note:&nbsp;The number of text strings in "Colors" is always equal to the number of text strings in its adjacent "Fruit" cell.&nbsp;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...IndexNameColorsFruit0BobYellowBanana0BobYellowApple0BobYellowGrape0BobYellowBlueberry0BobRedBanana0BobRedApple0BobRedGrape0BobRedBlueberryetc.&nbsp;&nbsp;&nbsp;

owenauger · Accepted Answer

HI&nbsp;paul_cranberry&nbsp;
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"
&nbsp;

Forum Discussion

Splicing together text string "pairs" from adjacent columns

1 Reply

Related content

Fabric Snowflake connector returns "Invalid credentials" with Key-Pair despite valid key pair

"Between" slicer with paired values: Oxygen/Nitrogen

Using Import Models and Dataflows Together

Synapse Link and Fabric Link together

PBI File together in App

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

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

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.