Query error editor

[cbouichi]

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?

[v-yulgu-msft · Accepted solution]

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

[cbouichi]

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.

[themistoklis]

Why dont you replace '-' with zero as it is a number column?

Is it going to cause you any problems?

[cbouichi]

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.

[PattemManohar]

@cbouichi Tried the same and able to replace '-' with 0

[cbouichi]

your replace values interface is different than my.

im using the last update. what version are you using?

or is this some configuration?

[themistoklis]

@cbouichi

It does work. Can you please share the workspace with us to see what is wrong?

Maybe it is another character there

[cbouichi]

https://1drv.ms/x/s!AuvIyAHe88OMa7EdHBh4btAYoNI

I got the same problem here but this time i want to delete the * to solve the errors. 

[themistoklis]

@cbouichi

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

[cbouichi]

@themistoklisthx for your patience but that doesnt work also.

[themistoklis]

@cbouichi

Are you trying to replace a value in the field which displays the error message?

[cbouichi]

Yes i did try that also.

[themistoklis]

@cbouichi

I mean if the field you are trying to replace the values has errors, then maybe it's best to fix the errors first??

[cbouichi]

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 ?

[themistoklis]

@cbouichi

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.

[cbouichi]

@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

[cbouichi]

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. 

[cbouichi]

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?