Power Query Keeps FIltering Rows Instead of Deleting them

[christeddy]

I'm working on a capstone project and there is a column that determines whether an entry is valid. I have looked for ways to delete the rows containing invalid entries as they are not necessary. Online, people keep saying "filter" but that isn't a permanent solution. I did use Excel to delete them eventually, but I don't want to believe that I can't do that with Power BI.

Also, the app doesn't get rid of null/blank values in another row. I have tried removing empty and I still get null values. I changed the data type from Numbers to Text, and now I just get blank values. I created a new column with the length, so I can then delete those that are equal to 0, but it isn't working. It keeps filtering instead of deleting.

I never knew data transformation could be this hard.

[pcoley · Accepted solution]

@christeddy 
Please try removing all non printable and extra characters first:
1. Select the Column you want to filter; select type: text in that column.
2.1 Transform> Format> Trim

2.2 Transform> Format > Clean
3. Filter empty cells

If this does not work please try Adding a Column where you can select the characters from the column you want to filter. 

4.1 Insert the next sentence if you want to keep letters and numbers:     
Text.Select([YourColumn], {"a".."z", "A".."Z", "0".."9"})

Finally filter empty cells.

I hope this helps. if so please mark it as a solution. Kudos are welcome.

4.2 Inster this sentence if you only want to keep numbers: 
Text.Select([YourColumn], {"0".."9"})

[christeddy]

That worked, but i got another problem lol. Thank You so much.

[pcoley]

@christeddy we are here to help each other. please create a new post with the problem and someone in the community will try to resolve the new issue.

[lbendlin]

A filter in Power Query truly removes the rows.  A filter in Power BI UI or in DAX "only" hides those rows.

Note that null/BLANK() is not the same as an empty string.  Also note that you may see (blank) when you have referential integrity violations.  That is by design.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...