none
Replace column values that contains text and numbers with new values RRS feed

  • Question

  • I have been trying to sort this out for several days with no success. Ultimately, I have a column of data that is not formatted in any particular way...we'll call that "REMARKS" in table "tbl RAW DATA".  It contains text and numbers.  The text values are separated by spaces and/or commas and the numbers I'm interested in are also separated from the text by spaces and/or commas.

    IE
    REMARKS
    AAA 3
    AAA, 3
    6 ABC
    6, ABC
    BBC

    I have a separate lookup table, "tbl LOOKUP" with two columns "REMARKS" and "DAYS" to look up the remarks from the first table and replace with the number of days associated with it.

    IE

    REMARKS DAYS
    3 3
    6 6
    BBC 3

    Originally, I transformed the column by replacing all spaces and commas with a caret (a character never used in all the random remarks), removing any double carets, and then splitting the column on the carets so I now have REMARKS.1, REMARKS.2, etc.  I then tried to do several different find and replace formulas, all with very mixed results.

    First I used John MacDougall's "Bulk Find and Replace in Power Query" https://www.howtoexcel.org/power-query/bulk-replace-values/ and I've tried doing it inside a Table.Transformation but every time it either gives me the original value or puts a null in for the values that do exist in the lookup and keeps all of the other original values.  

    I ultimately want one column with the number of days added to "tbl RAW DATA" or replacing the "REMARKS" column.  The REMARKS is only there as a method of getting the number of days.  I think my main problem is coming from the fact that the RAW DATA REMARKS column contains both text and numbers and I'm running into type errors.

    This one has got me stumped.  I hope I've provided enough information that my question makes sense to someone, unfortunately I can't provide real data samples. I'd appreciate any insight that could be provided.

    Sunday, June 2, 2019 4:34 PM

Answers

  • I figured out a method that serves my purpose. It's neither elegant or efficient but it does what I need it to. While I didn't provide a lot of data I felt the sample was sufficient.... Take a column with multiple data chunks in it, iterate through the chunks, determine if any of the chunks are in the lookup table and return the corresponding value in it's place. If all chunks were text...easy...if all chunks were numbers....easy, but not as easy when they are mixed.

    Could a moderator please delete? Thanks

    • Marked as answer by nilber Monday, June 17, 2019 3:53 AM
    Monday, June 17, 2019 3:52 AM

All replies

  • Hi there. Can you share the M formula text of a simple repro, showing what you've tried to do that didn't work?

    Ehren

    Friday, June 14, 2019 7:12 PM
    Owner
  • Hi nilber,

    it is not clear to me what your exact requirements are. Your sample data looks a bit too simplified to me: How about partial matches and capitalisation?

    Please check out this blogpost to get an idea of some possible aspects of a task like yours: https://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, June 16, 2019 8:38 PM
    Moderator
  • Excel 365 PQ
    Can't figure out what you want,
    or why you can't provide sanitized real data samples.
    But here is a way to clean up a column
    with random text/numbers/punctuations
    and arrange the data into dedicated columns.
    http://www.mediafire.com/file/6j7a4mkj5md2olz/06_16_19a.xlsx/file
    http://www.mediafire.com/file/o6ff8srm6w1lcb6/06_16_19a.pdf/file

    Monday, June 17, 2019 3:36 AM
  • I figured out a method that serves my purpose. It's neither elegant or efficient but it does what I need it to. While I didn't provide a lot of data I felt the sample was sufficient.... Take a column with multiple data chunks in it, iterate through the chunks, determine if any of the chunks are in the lookup table and return the corresponding value in it's place. If all chunks were text...easy...if all chunks were numbers....easy, but not as easy when they are mixed.

    Could a moderator please delete? Thanks

    • Marked as answer by nilber Monday, June 17, 2019 3:53 AM
    Monday, June 17, 2019 3:52 AM
  • Thanks for your ingenious solution
    to a problem that escaped us all.
    For the sake of future "Me-Too"s
    please don't ask the moderator
    to delete your revelations.

    Tuesday, June 18, 2019 1:30 AM