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 record...
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

     

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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Yes indeed ibarrau, as mentioned I've modified the post so it should be more clear now 🙂