Forum Discussion
Helper IIDeleting row where column contains text
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
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
MaggieCommunity 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.
9 Replies
- Nathaniel_C
Community Champion
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.
NathanielThis 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
v-juanli-msftCommunity Support
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
MaggieCommunity 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.
Whibley92I 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?
Anupa_JayakodyHelper I
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
