Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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
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
Proud to be a Super User!
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
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.
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?
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
Proud to be a Super User!
This worked for me, thank you!
Hi @spaceyjones ,
You are welcome. Please mark as a solution so that others may find this more quickly!
Nathaniel_C
Proud to be a Super User!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |