Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter out rows with sapces inbetween

I have a table with a column with these values. (these values are just example values. The real values are random)

 

  • ABCD
  • JZIEKFFHE
  •  
  • HELLO WORLD
  • JDJKA
  • MISTER FUNNY
  • HELLO PBI

 

The records HELLO WOLRD, Mister Funny, and HELLO PBI I need to filter out. The blank record needs to stay.

I tried filtering the column when it contains a space, but then the blank value also gets filtered out.

Need Help
  • Vvelarde's avatar
    Vvelarde
    6 years ago

    Anonymous 

     

    You could try this

     

    = Table.SelectRows(#"Changed Type", each not Text.Contains([Column]," "))

     

    if the blank rows are also filtered out that means that are not real BLANKS , maybe had spaces.

     

     

    Regards

     

    Victor

     

5 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    6 years ago

    Hi. Let's check what power query can do. If you click on the down arrow next to the column you can find the filter with different options. Try using the "doesn't contains". Then complete it with "HELLO". It should look like this:

    = Table.SelectRows(#"Last Step", each not Text.Contains([Column1], "HELLO"))

    That will avoid all values with HELLO on it. If you need to be more specific try the complete phrase in doesn't contains for both cases "HELLO WORLD" and "HELLO PBI".

     

    Hope this help,

    Regards, 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Thank you for your reply. The example values I gave are not the real values. The words are more random and do not always start with HELLO. I've modified my example.

      • ibarrau's avatar
        ibarrau
        Super User
        6 years ago

        Sorry SSS95 but we can only help you with the data you gave us. If you want to show us more in order to help, then do it 🙂
        You can still use the function to filter avoiding what the text contains as I have shown. You can find the patterns of your data to clean it up with that example.