Merged three columns but cannot work out how to remove delimiter - think it needs custom M code

[Rhothgar]

I gave up trying to use nested tables. Way too complex for a newbie so ditched trying to use XML format.

Made fantastic progress today but am now properly stuck.

I've merged three columns using ; as a delimiter but the null cells cause the delimiter to be used where it is not required.

Tried many ways to remove them to no avail.

Before Merge!

After Merge!

 
I only want to remove ; ; after singular column Arts & Crafts, Technology & Gadgets, Health & Beauty and so on (NB they are a legacy of the three columns being merged into one) and the singular semi-colon after Technology & Gadgets on Row 11 (this is a legacy of three columns being merged into one).

I noticed the lack of null figures in the 2nd Column pre-merge.  Perhaps this is a clue but I wouldn't have a clue how to decipher it.



Syntax required is:-

Row 1 Arts & Crafts
Row 5 Home & Living, Technology & Gadgets, Toys & Entertainment
Row 6 Technology & Gadgets
Row 8 Health & Beauty
Row 11 Home; Technology & Gadgets

Here is my example source code:-

= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwqKVaIKTUwMDJTcC5KTCsptlawVorVobaMR35uKkzGJ7MsMy/dWiEkNTkjLz8nP70SJuOemJKeCtIUkl8JN8g1ryS1qCQxMy83Na8EbBhOjUTIeqQm5pRkwGScUhNLSyoJuJ2AgUCf4fGLUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Categories = _t])

Here is my code for the last two steps:-

    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value3",{"Categories.1.1", "Categories.3"}),
    #"Merged Columns1" = Table.CombineColumns(#"Removed Columns",{"Categories.1.2", "Categories.2.1", "Categories.2.2"},Combiner.CombineTextByDelimiter("; ", QuoteStyle.None),"Categories")

 
Thanks in advance.  I feel I've come a long way in 3 - 4 days but still stacks to learn.

[spinfuzer]

Merge with ";" only instead of "; "

Text.TrimEnd([Column], ";")

After that replace ";" with "; " if you wanted the extra space after the semicolon.

[Rhothgar]

Thanks.

Looks good but where do I put it please?

= Table.TransformColumns(#"Merged Columns1",{{"Categories", Text.TrimEnd([Column], ";"), type text}})

[spinfuzer · Accepted solution]

= Table.TransformColumns(#"Merged Columns1",{{"Categories", each Text.TrimEnd(_, ";"), type text}})

[Rhothgar]

OK. Back to where I was and looking good apart from I cannot find where I entered "; " originally so I still have ";" at the end of Arts & Crafts lines only


EDIT!

Found it!  It was way, way back in the steps.

I need to learn how to clean the code. I have far too many steps.

Many thanks for helping me with this, this evening.

I am very tired so need to go to bed else I will make more mistakes then wake up fresh.

[Rhothgar]

Thanks again.

Sadly, I think I've messed it all up as I tried several things instead of waiting for a reply.

I need to try and work out where I was...