How do you get rid of extra delimiters when merging columns?

[Monsignor]

In a situation where not every column has a value, the Merge Columns feature adds a delimiter even if a cell is blank. Thus a result can be like:

IL,KS,,,,NY
,,,,,FL
,CA,,,,
GA,FL,,DC,KS,CA

rather than:
IL,KS,NY
FL
CA
GA,FL,DC,KS,CA


Basically, it'd be nice to recreate the new TEXTJOIN function and include the option to Ignore Blanks.

 

[Ozeroth]

Oh yep sure.
In this case, the (_) represents the List argument of the "Combiner" function I have defined as the 3rd argument of Table.CombineColumns.
The "each _" syntax is a simplified way of defining a function that takes one argument (see section 9.7 of language specification).

The 3rd argument of Table.CombineColumns (the "Combiner" function) must be a function that takes a single List argument, and whatever this function returns will be put in the merged column.

The List argument that is passed to the Combiner function, for each row, will be the list of values from the fields that you have chosen to combine (specified in the 2nd argument of Table.CombineColumns).

Basically, the Combiner function in this case in words is "First apply List.RemoveNulls to the list (represented by _), then combine the result with comma delimiter".

These two versions of the Combiner function are equivalent.

1. ( ListOfItems ) => Combiner.CombineTextByDelimiter(",", QuoteStyle.None)(List.RemoveNulls( ListOfItems ))
2. each Combiner.CombineTextByDelimiter(",", QuoteStyle.None)(List.RemoveNulls( _ ))

Cheers,
Owen

 

[Monsignor]

It sounds like a sideways loop, taking each column one at a time.

 

[Monsignor]

Thanks for the help Ozeroth. I made a video showing my hack solution and your more elegant M solution.


https://www.youtube.com/watch?v=7JctwBVden4&feature=youtu.be