Power Query filter works on all records except one.

[Anonymous]

Hi PBI community, 

I have a list of on-hire requests of which I filter based on the system log to not end with "Logically Deleted." It works for every record except for this one. You can see in the bottom pane the record clearly ends with "Logically Deleted." And yet it's still there?

I've also tried ends with "Deleted." but this doesn't work either. Any ideas? 

Thanks, 

[vivran22]

Hi,

Is it possible to share the data file/sample pbix file to review it in details?

Rgds,
Vivek

[Anonymous]

Hi Vivek, 

Yep, here's a link to a truncated version. 

The problem record is ID 4338. 

https://drive.google.com/file/d/1_iGxnpRFYQRn4d5kw0ag6YhsF38N0nVO/view?usp=sharing

Let me know if the link doesn't work. 

[HotChilli · Accepted solution]

Hi, the link works fine but we don't get access to the source data (that's powerbi security kicking in), we just have the data once it is loaded into power bi.

I copied the table and opened 'Power Query', pasted it in to 'Enter Data' and ran your 'remove text' command, and it worked fine. 

So we would need to see the original data.

Usually these things are due to spaces or non-printable characters so you could experiment with Trim and Clean or TrimEnd

[Anonymous]

Try this for the source data. 

https://drive.google.com/file/d/1xSAolHmFPWrcvdrh2zVpmTZq2ao1ayTk/view?usp=sharing

Just tried both Trim and Clean, seems to have worked. Thanks!

I'd be curious as what caused it?

[vivran22]

You can mark your post as solution. It will help others also