none
If Contains or overwrite RRS feed

  • Question

  • Hi Guys,

    I had a table with a field that contains a lot of information, but not always in the same order or logic. Therefore I was not able to split the column accordingly.

    I was looking for a way to add a new column and then filter out the information that I needed. But could't find an easy solution.

    1.Option: Search for "String" and replace whole field with this "String". Basically "Overwrite" the field.

    Is that possible??

    2. Option: If contains, then write in new column. I used "Text.Contains". But then only have the Logical TRUE/FALSE Value. Then I did some replacing afterwards and was able to have seperate columns for most of the words I needed. 

    But helpful would also be, if I have two different strings that need to appear in the same column. So if contains A OR B.

    How is that possible?

    Would be thankful for easy solutions. Don't use power query on a daily basis ;)

    Thank you!

    Cheers 

    Tanja

    Friday, April 17, 2015 4:46 PM

Answers

  • Hi Tanja

    Yes, you can change the above statement to become:

    Table.AddColumn(tableName, "newColumnName", each if Text.Contains([column1], "A") then "A" else if Text.Contains([column1], "B") then "B" else [column1]) :)


    Thanks, Hadeel

    • Marked as answer by Locke583 Tuesday, April 21, 2015 8:07 AM
    Monday, April 20, 2015 4:55 PM

All replies

  • Hi Tanja,

    If I understand correctly, you want to do something like:

    If column1 contains text 'A' or 'B' then replace cell value with string 'C'? If that's the case then try:

    - Click on the filter button on the column you want to filter (it's on the upper right side next to the column name).

    - In the drop down choose Text Filters then Contains

    - You will get another window where you can build your filter. Select contains and the 'or' radio button then fill in the text boxes with A and B strings then click ok.

    - In the editor home tab there is a button called Advanced Editor, click on it.

    You will see that the previous filter step created query that looks something like:

    Table.SelectRows(tableName, each Text.Contains([column1], "A") or Text.Contains([column1], "B"))

    To add the step (if then) logic, edit that text to be:

    Table.AddColumn(tableName, "newColumnName", each if Text.Contains([column1], "A") or Text.Contains([column1], "B") then "newString" else [column1])

    then click ok :)

    Let me know if that helps or if I misunderstood your question :)

    Thanks,

    Hadeel

    Friday, April 17, 2015 7:34 PM
  • Hi Hadeel,

    thanks so much for your quick reply. 

    That works pretty well for most of my cases.

    I can probably adjust that logic somehow to also cover the case:

    If contains A then write A; if contains B then write B; else write C. 

    Right?

    Haven't quite figured out how yet, though. I would be very thankful if you have another quick and easy adaptation. :)

    Thank you!

    Tanja

    Monday, April 20, 2015 8:23 AM
  • Hi Tanja

    Yes, you can change the above statement to become:

    Table.AddColumn(tableName, "newColumnName", each if Text.Contains([column1], "A") then "A" else if Text.Contains([column1], "B") then "B" else [column1]) :)


    Thanks, Hadeel

    • Marked as answer by Locke583 Tuesday, April 21, 2015 8:07 AM
    Monday, April 20, 2015 4:55 PM
  • Thank you so much Hadeel.

    Works great.

    :)

    Tuesday, April 21, 2015 8:09 AM