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.

 

[FDibbins]

few ways come to mind - 1 using SUBSTITUTE, another structuring a formula to not add them in the 1st place (also using substitute)

What does your raw data look like?

 

[Monsignor]

Let's say I've done a lot of work in Power Query to get to this step and now I don't care about the Day 1 thru Day 7 headers. I just want all of the values pushed to the left.

 

[FDibbins]

Don't know if this will work in Query, but this is what I use if I need to do this...
=SUBSTITUTE(TRIM(B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10)," ",",")

 

[Monsignor]

That won't work in Power Query. In native Excel it'd be very easy to use TEXTJOIN and set it to "ignore blanks." Power Query makes a big mess.

 

[FDibbins]

OK well good luck, I hope someone can help you with this

 

[Monsignor]

Thanks for the effort. PQ is messy in a lot of ways. Maybe in one of the monthly updates this problem with these excess delimiters will be fixed.

 

[Ozeroth]

Hi Monsignor,

This is one of those situations where I think you just have to modify the code generated by the interface.

I assume after you choose "Merge Columns" you get a line of code like this:

= Table.CombineColumns(PreviousStep, {"Day 1", "Day 2", "Day 3", "Day 4", "Day 5"}, [COLOR=#ff0000][B]Combiner.CombineTextByDelimiter(",", QuoteStyle.None)[/B][/COLOR], "Merged")

You can change the code as follows, to instead apply the Combiner function to the list with nulls removed:

= Table.CombineColumns(PreviousStep, {"Day 1", "Day 2", "Day 3", "Day 4", "Day 5"}, [B][COLOR=#ff0000]each Combiner.CombineTextByDelimiter(",", QuoteStyle.None)(List.RemoveNulls(_))[/COLOR][/B], "Merged")

 

[Monsignor]

Ozeroth! YES!!! that's it! Thanks. Now, I have to study this and see how it works. But this is it.

 

[Monsignor]

What work is being done by (_) ?