Overview of Combiner.CombineTextByPositions

The Combiner.CombineTextByPositions function allows you to combine text values from different columns by specifying the positions of the columns. For example, if you have three columns (Column1, Column2, and Column3), you can use the function to combine the values in Column1 and Column3 into a single column, while ignoring the values in Column2.

The function takes three arguments:

– The table to operate on

– A list of positions (in the form of integers) that specify the columns to combine

– An optional delimiter to use between the combined values (default is a comma)

Here is the basic syntax of the function:

Combiner.CombineTextByPositions(table as table, positions as list, optional delimiter as text)

let

CombineTextByPositions = (table as table, positions as list, optional delimiter as text) =>

let

columns = Table.ColumnNames(table),

selectedColumns = List.Select(columns, each List.Contains(positions, columns.IndexOf(_))),

combinedColumnName = “Combined”,

combinedColumn = Table.AddColumn(table, combinedColumnName, each Text.Combine(List.Transform(List.Select(selectedColumns, each not List.IsEmpty(_)), each Text.From(_)), delimiter), type text),

removedColumns = List.RemoveFirstN(List.RemoveItems(columns, selectedColumns), 1),

reorderedColumns = List.InsertRange(List.InsertRange(removedColumns, positions, selectedColumns), 0, {combinedColumnName}),

outputTable = Table.SelectColumns(combinedColumn, reorderedColumns)

in

outputTable

in

CombineTextByPositions

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMlSK1YlWMjI3VIrKzSspVrISkkxMSWnKzUnNyktSgDmJ4Q==”, BinaryEncoding.Base64)), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#”First Name” = _t, #”Last Name” = _t, #”Email Address” = _t]),

#”Combined First and Last Names” = Combiner.CombineTextByPositions(Source, {0, 1}),

#”Removed Email Address” = Table.SelectColumns(#”Combined First and Last Names”, {“Combined”, “Email Address”})

in

#”Removed Email Address”

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45Wqk8vyklR0lEyMjQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyVrAwN9EokxJbIzUwM9DQAA==”, BinaryEncoding.Base64)), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#”Column1″ = _t, #”Column2″ = _t, #”Column3″ = _t, #”Column4″ = _t, #”Column5″ = _t, #”Column6″ = _t]),

#”Combined Columns 2, 4, and 6 with Custom Delimiter” = Combiner.CombineTextByPositions(Source, {1, 3, 5}, “|”),

#”Removed Other Columns” = Table.SelectColumns(#”Combined Columns 2, 4, and 6 with Custom Delimiter”, {“Combined”})

in

#”Removed Other Columns”