Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Where to add a Text.RemoveRange function in M / Power Query

I understand the syntax of Text.RemoveRange but I am not sure how to add this into M / Power Query.

Scenario: TableX has a column called "Departure", which is an ID that features a leading zero. I need to remove that leading zero. So instead of "09999", "02345", etc.,  I want "9999", "2345", etc.. 

I thought it may be preceded by something like a Table.Transformrows in M e.g.,

#"Drop Lead Zero" = Table.TransformRows(#"Changed Type", Text.RemoveRange([Departure], 0, 1))

 

But this (and many other attempts) does not work. So I guess I am struggling with where to position the function in M.

 

Your advice is appreciated! 

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi Anonymous ,

     

    You don't want TransformRows, you want TransformColumns, something like this:

    #"Drop Lead Zero" =
Table.TransformColumns(
    #"Changed Type",
    {
        { "Departure", each Text.RemoveRange(_, 0, 1) }
    }
)

     

    Pete

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi Anonymous ,

     

    You don't want TransformRows, you want TransformColumns, something like this:

    #"Drop Lead Zero" =
Table.TransformColumns(
    #"Changed Type",
    {
        { "Departure", each Text.RemoveRange(_, 0, 1) }
    }
)

     

    Pete