locked
Replace Strings RRS feed

  • Question

  • Is there a way in Power Query to replace the contents of a string NOT CONTAINING a keyword or phrase?
    Friday, October 17, 2014 8:47 PM

Answers

  • JJ,

    See if this helps. Using Power Query, I created an intermediary column to search for the string I am lookin for ("ipsum" in my case). Then wher "NOT FOUND" the final column replaces the whole sting with something else ("NOT FOUND" in my case).

    See Power Query worksheet.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Added Custom" = Table.AddColumn(Source, "FindString", each Text.Contains([String],"ipsum")),
        #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"FindString", type logical}}),
        #"Added Custom1" = Table.AddColumn(#"Changed Type", "ReplaceNotFound", each if not [FindString] then "NOT FOUND" else [String])
    in
        #"Added Custom1"


    Regards, Avi www.powerpivotpro.com

    • Marked as answer by jj15164555 Monday, October 20, 2014 8:52 PM
    Saturday, October 18, 2014 8:01 PM

All replies

  • So you'd like to replace the entire string with new text if the string doesn't contain a given keyword or phrase?
    Saturday, October 18, 2014 12:48 AM
  • JJ,

    See if this helps. Using Power Query, I created an intermediary column to search for the string I am lookin for ("ipsum" in my case). Then wher "NOT FOUND" the final column replaces the whole sting with something else ("NOT FOUND" in my case).

    See Power Query worksheet.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Added Custom" = Table.AddColumn(Source, "FindString", each Text.Contains([String],"ipsum")),
        #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"FindString", type logical}}),
        #"Added Custom1" = Table.AddColumn(#"Changed Type", "ReplaceNotFound", each if not [FindString] then "NOT FOUND" else [String])
    in
        #"Added Custom1"


    Regards, Avi www.powerpivotpro.com

    • Marked as answer by jj15164555 Monday, October 20, 2014 8:52 PM
    Saturday, October 18, 2014 8:01 PM
  • Yes, that's correct.  For example, for every cell in a column NOT containing a phrase  word "TEST", I'd like to change the contents of the cells to "PRODUCTION".  Thanks.
    Monday, October 20, 2014 2:00 PM
  • Avi's example can be simplified a little bit to something like

    =Table.TransformColumns(Source, {"ColumnName", each if Text.Contains(_, "test") then _ else "Production"})

    If it needs to be case-insensitive, I think you have to say

    = Table.TransformColumns(Source, {"Column2", each if Text.Contains(Text.Lower(_), "test") then _ else "Production"})

    Monday, October 20, 2014 2:15 PM
  • Add a custom column and enter the following:

    = if not Text.Contains([columnName],"TEST")  then "PRODUCTION" else [columnName]

    where columnName is the name of the column containing the text you want to check.

    Monday, October 20, 2014 2:22 PM
  • Whoops, appears that Curt submitted his solution while I was entering mine. :)
    Monday, October 20, 2014 2:25 PM
  • Thanks so much for your response!
    Monday, October 20, 2014 2:54 PM