Deleting row where column contains text

[nunnc01]

Hi,

I have a column of data coming in to Power BI from our system that should only contain numbers. However somehow a piece of text has been stored which is now preventing me from having the column saved as a number and stopping me from being able to sum the numbers.

How do I insert a step in Power Query that would delete any rows where this column contains anything other than a number or a null ? I know I could manually delete the one row but I would like to build in a step so that it automatically deals with any future issues like this.

Thanks

[Anupa_Jayakody]

Hi, I have some rows where I want to remove the rows contatining some text values, 

example- I have rows with texts  like "frieght", "reels", and I want to remove those rows automatically, Column type is "text",  I only want to remove the rows which contains these texts

Does anyone know how to do this

[Nathaniel_C]

Hi @Anupa_Jayakody 

Easy go to Power Query click on the down arrow in the header of the column you wish to filter.  Click on those you wish to delete, and like magic, gone.  The nice thing is that you will see a filter icon when it is done.  You can also remove this step, or modifiy it by either clicking on X by the step or the gear icon.


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Anupa_Jayakody]

Thank you, but I want this to be performed every time when I add a new file, so I dont have to do this manually, and the filter should pick lines only without those specified words in the column 

For example- I want to remove any row that contains "XYS" text anywhere in the cells in that column. It could be even text "XYS" is with some other words too, but need to be removed if it is there in that column

[v-juanli-msft · Accepted solution]

Hi @nunnc01 

Assume you have table

Format->Trim (Returns the result of removing all leading and trailing whitespace from text value text.

Change type to whole number

Replace error with null

Finally, remove null.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Whibley92]

I am trying to perform this on a column with only 2 sets of criteria (I'm very new to Power BI). I would like to permanently deleted the rows with one particular category, rather than just filtering out as there is a lot of data. How do I perform this?

[Nathaniel_C]

Hi @nunnc01 ,

Click on the type and make a number. That will give you a error

Home - Remove Rows - Remove Errors

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[spaceyjones]

This worked for me, thank you!

[Nathaniel_C]

Hi @spaceyjones ,

You are welcome.  Please mark as a solution so that others may find this more quickly!

Nathaniel_C

[Nathaniel_C]