Forum Discussion

JuanBolas's avatar
JuanBolas
Icon for Helper II rankHelper II
4 years ago
Solved

Help with function to clean up data - replace

Hello,   I'm new to Power BI and Power Query.   I want to clean up my data a little bit before going into BI.   My table has about a dozen fields but I just want to concentrate on one called Co...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Use the Transform tab rather than the Add Column tab if you don't want a new column for each step.

  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You might need to write some more customized logic like this:

    = Table.TransformColumns(
    #"Replaced Value4",
    {{"Input", each
      if Text.End(_, 3) = " SA" or Text.End(_, 4) = " SRL"
      then Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd})
      else _, type text}}
)
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

Use the Transform tab rather than the Add Column tab if you don't want a new column for each step.

  • JuanBolas's avatar
    JuanBolas
    Icon for Helper II rankHelper II
    4 years ago

    Worked great AlexisOlson.

     

    How do you suggest I remove the trailing SA and SRL at the en of some names? SRL works ok with replace but a replace of SA or " SA" (space SA) changes things that shouldn't be changed.

     

    Thanks in advance

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      4 years ago

      You might need to write some more customized logic like this:

      = Table.TransformColumns(
    #"Replaced Value4",
    {{"Input", each
      if Text.End(_, 3) = " SA" or Text.End(_, 4) = " SRL"
      then Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd})
      else _, type text}}
)