Delete an entire row if one column contains any number

[Anonymous]

I have a connection to Active Directory and I want to limit the entries to indviduals only and exclude any group and test users.  How can I exclude any row where the displayname contains an integer?

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You can try the following solution.

When you filter values out of a column in the query editor, it's essentially the same as deleting them. Those values will not be loaded to your data model.

Best Regards,

Eads

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amol0512]

i am getting error while loading query data. I checked the data and found that there are 2 rows where there are numbers. Is there any way we can filter out >=0 in power query. Right now i am not getting numeric filter options, only text filters are avilable in the drop down menu. Even if i select curent numeric static values in filter it will not work if other number pops up in future. Actually I am using files from folder. Looking for dynamic solution. please suggest. Thanks in advance. 

[tex628]

Do you want to delete them in power query or remove them with filters in report view?

[Anonymous]

I don't mind.  I would prefer to exclude them in Power Query and not bring them in at all but if that can't be done then filter them out in report view.

[tex628]

You you create a duplicate of the column you can then do a "Split column by non digit to digit".

This will split all the rows and you will be able to identify exactly which rows have a combination of characters and numbers.



The rows that do not have numbers should return null which means that you should keep them. 

[tex628]

[Anonymous]

Thanks, I did consider this but I was hoping to find a more elegant solution. 

[tex628]

I think thats the most friendly solution that I can think of! Otherwise i believe you need to iterate through the characters with a custom column.