locked
Removing trailing characters RRS feed

  • Question

  • Hi : I was wondering what would be the M code for removing any trailing characters such as comma or commas in this instance as per below? In the first row there is no comma in the end so nothing to remove. But in the in second row there are. Thanks.

    Merged
    sdfs, sdfsd, sdfsd
    wew, ere, ere, ,
    cvcv, ere, oioi,
    zxzx rerte erte   rete, ,
    tyty, erer,
    wew tyt ere, ,
    Output
    Merged
    sdfs, sdfsd, sdfsd
    wew, ere, ere
    cvcv, ere, oioi
    zxzx rerte erte   rete
    tyty, erer
    wew tyt ere

    Tuesday, January 14, 2020 5:18 AM

Answers

  • Hi M.Awal:

    the code as follows:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangeType = Table.TransformColumnTypes(Source,{{"Merged", type text}}),
        TrimEnd = Table.TransformColumns(ChangeType,{"Merged",each Text.TrimEnd(_,{" ",","})})
    in
        TrimEnd

    • Marked as answer by M.Awal Tuesday, January 14, 2020 9:08 AM
    Tuesday, January 14, 2020 6:11 AM

All replies

  • Hi M.Awal:

    the code as follows:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangeType = Table.TransformColumnTypes(Source,{{"Merged", type text}}),
        TrimEnd = Table.TransformColumns(ChangeType,{"Merged",each Text.TrimEnd(_,{" ",","})})
    in
        TrimEnd

    • Marked as answer by M.Awal Tuesday, January 14, 2020 9:08 AM
    Tuesday, January 14, 2020 6:11 AM
  • Hi ziying35

    Excellent. Thanks. 

    Tuesday, January 14, 2020 9:08 AM
  • Hi M.Awal,

    Here's a bit of a fishing lesson too:

    https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-reference

    The MSDN website has a great resource for finding solutions.  Just go to the "text functions" because you are having a "text" problem, and then in your text you said you wanted to "remove trailing characters", which would be Trim (Just like Excel), these help you zero down to the Text.TrimEnd function that ziying35 pointed out.

    I use this site all the time.  There is one for DAX too if you are interested.

    https://docs.microsoft.com/en-us/DAX/


    John Thomas

    Wednesday, January 15, 2020 5:33 PM
  • Thx for sharing that. I am familiar with this website and refer to it when I need it. As per the definition for Text.TrimEnd, it specifically mentioned about only white space not any characters. I didn’t realise it could be used for any characters
    Wednesday, January 15, 2020 9:05 PM