Forum Discussion

wad11656's avatar
wad11656
Advocate I
3 years ago
Solved

Text.Combine() with incrementing (numerical) delimiter

Is there a way to make Text.Combine() use an incrementing delimiter?

 

For example, change the "||" below to an incrementing number that starts from 1?

{"NamesColumn (Combined)", each Text.Combine([#"NamesColumn"]," || "), type text}

 

So instead of a result like:

Betty || Mark || John || Alice

 

I'd like something like:

(1) Betty (2) Mark (3) John (4) Alice

 

Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago

    Hi wad11656 ,
    yes, this can be done with the List.Zip-function:

    let
  Source                   = {"Betty", "Mark", "John", "Alice"}, 
  ListWithNumbers          = {1 .. List.Count(Source)}, 
  FormattedListWithNumbers = List.Transform(ListWithNumbers, each "(" & Text.From(_) & ") "), 
  ZipAndExpand             = List.Combine(List.Zip({FormattedListWithNumbers, Source})), 
  ToText                   = Text.Combine(ZipAndExpand, " ")
in
  ToText

2 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    Hi wad11656 ,
    yes, this can be done with the List.Zip-function:

    let
  Source                   = {"Betty", "Mark", "John", "Alice"}, 
  ListWithNumbers          = {1 .. List.Count(Source)}, 
  FormattedListWithNumbers = List.Transform(ListWithNumbers, each "(" & Text.From(_) & ") "), 
  ZipAndExpand             = List.Combine(List.Zip({FormattedListWithNumbers, Source})), 
  ToText                   = Text.Combine(ZipAndExpand, " ")
in
  ToText
  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    Or if you prefer a solution that does more in table-mode, you could use this:

    let
  Source = {"Betty", "Mark", "John", "Alice"}, 
  #"Converted to Table" = Table.FromList(
    Source, 
    Splitter.SplitByNothing(), 
    null, 
    null, 
    ExtraValues.Error
  ), 
  #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1, Int64.Type), 
  #"Reordered Columns" = Table.ReorderColumns(#"Added Index", {"Index", "Column1"}), 
  Custom1 = #"Reordered Columns", 
  AddBrackets = Table.TransformColumns(
    Table.TransformColumnTypes(Custom1, {{"Index", type text}}, "en-US"), 
    {{"Index", each "(" & _ & ")", type text}}
  ), 
  Custom2 = AddBrackets, 
  Custom3 = List.Combine(Table.ToRows(Custom2)), 
  Custom4 = Text.Combine(Custom3, " ")
in
  Custom4