- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Query error editor
Hallo everybody,
I have some rows that contains some simple errors.
some colums have the number properties. in some of the rows are filled with -.
This creates an error. so i want to replace - with empty cell but cant replace it with the command replace values.
Because when i type - it will not except the input because its not a number.
What is the correct way to solve this kind of errors?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @cbouichi,
Please change the data type of that field from "Any" to "Text" before replacing values. Then, you can replace "-" with "0" or empty. After replacing, you can change the data type to "Whole number".
Best regards,
Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @cbouichi,
Please change the data type of that field from "Any" to "Text" before replacing values. Then, you can replace "-" with "0" or empty. After replacing, you can change the data type to "Whole number".
Best regards,
Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
i solved it in the source tabel. thats not the way but the way you suggested didn't work for me.
But everybody thanks for your patience with me.
i will change the data in the source tabel in excel and refresh power bi table.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Why dont you replace '-' with zero as it is a number column?
Is it going to cause you any problems?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
in the replace values command it will not accept '-' as a value to find and replace.
it most be a number to find and replace.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@cbouichi Tried the same and able to replace '-' with 0
Did I answer your question? Mark my post as a solution!
Proud to be a PBI Community Champion
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
your replace values interface is different than my.
im using the last update. what version are you using?
or is this some configuration?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It does work. Can you please share the workspace with us to see what is wrong?
Maybe it is another character there
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
https://1drv.ms/x/s!AuvIyAHe88OMa7EdHBh4btAYoNI
I got the same problem here but this time i want to delete the * to solve the errors.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Probably it is not the '-' that you need to replace but it is the blanks.
So in find replace leave the find text with no value and the replace text with 0
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes i did try that also.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I mean if the field you are trying to replace the values has errors, then maybe it's best to fix the errors first??
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes @themistoklis this is what i try too do. the error is there because its not a number. So i want to replace that value with something els. but the question is how ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It is an error message which means that there is something wrong with this field.
It is not a text that can be replaced.
You have to fix the error message. You need to find out what is causing it.
The word 'error' is not a value in the column but rather a message from the software.
I hope it makes sense.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@themistoklisi try too explain that its a error in the field that is cause by the value field. In this case '*' or '####' or '-'
it is a text that is placed in a cell that is a part of a number column.
i understand that error is not a value in the cell
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have noticed that power bi table removed all '-' values except for 2 cells that have a cell property in the source excel as a number.
the other '-' values have in the source excel table a property as standard.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I would like too share it. But i got alot of sheets in it. Will 1 excel sheets will be enuff?
Maybe this is the source of the problem?

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
03-02-2024 03:37 AM | |||
08-15-2024 04:20 AM | |||
09-24-2024 02:28 AM | |||
07-28-2024 06:12 PM | |||
09-10-2024 07:35 AM |
User | Count |
---|---|
141 | |
115 | |
82 | |
63 | |
48 |