Forum Discussion

Rhothgar's avatar
Rhothgar
Icon for Helper IV rankHelper IV
2 years ago
Solved

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

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.

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

5 Replies

  • Merge with ";" only instead of "; "

     

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

     

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

    • Rhothgar's avatar
      Rhothgar
      Icon for Helper IV rankHelper IV

      Thanks.

      Looks good but where do I put it please?

      = Table.TransformColumns(#"Merged Columns1",{{"Categories", Text.TrimEnd([Column], ";"), type text}})
      • spinfuzer's avatar
        spinfuzer
        Icon for Solution Sage rankSolution Sage
        = Table.TransformColumns(#"Merged Columns1",{{"Categories", each Text.TrimEnd(_, ";"), type text}})