none
Replace text conditionally RRS feed

  • Question

  • One of the columns in Excel file contains a  "Manufacture" column. It has data like "F5", "F5 Networks" or "F5 Networks, Inc". For my usage, I would like to replace any "Manufacture" field contains "F5" to just "F5". Another example is replacing "Cisco", "Cisco Systems" or "Cisco Systems, Inc" to just "Cisco". how can I do it?
    Tuesday, December 12, 2017 3:17 PM

Answers

  • You can add the following step to your query:

    TransformedColumn = Table.TransformColumns(<PreviousStepName>, {"Manufacture", each List.First(Text.SplitAny(_, """ "","))})
    Note that the second parameter in Text.SplitAny, which are the characters to split by, is inconsistent with most other functions, which take a list of values. Instead of being a list of split characters, like {" ", ","} (space or comma), the parameter is text with no delimiter between split characters, hence """ "",".

    • Marked as answer by David Wong 08 Thursday, December 14, 2017 5:03 AM
    Wednesday, December 13, 2017 3:00 PM

All replies

  • You can add the following step to your query:

    TransformedColumn = Table.TransformColumns(<PreviousStepName>, {"Manufacture", each List.First(Text.SplitAny(_, """ "","))})
    Note that the second parameter in Text.SplitAny, which are the characters to split by, is inconsistent with most other functions, which take a list of values. Instead of being a list of split characters, like {" ", ","} (space or comma), the parameter is text with no delimiter between split characters, hence """ "",".

    • Marked as answer by David Wong 08 Thursday, December 14, 2017 5:03 AM
    Wednesday, December 13, 2017 3:00 PM
  • Thanks, it worked for me
    Thursday, December 14, 2017 5:04 AM