Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
cbouichi
Helper IV
Helper IV

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?

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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".

1.PNG2.PNG3.PNG

 

Best regards,

Yuliana Gu

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

View solution in original post

18 REPLIES 18
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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".

1.PNG2.PNG3.PNG

 

Best regards,

Yuliana Gu

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

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
Community Champion
Community Champion

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

 

Is it going to cause you any problems?

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.

 

error bi.JPG

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

 

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




your replace values interface is different than my.

 

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

or is this some configuration?

@cbouichi

 

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

Maybe it is another character there

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

 

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

@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

@themistoklisthx for your patience but that doesnt work also.

 

BI error.JPG

@cbouichi

 

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

Yes i did try that also.

@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??

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 ?

@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.

@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

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. 

I would like too share it. But i got alot of sheets in it. Will 1 excel sheets will be enuff?power bi relaties.JPG

Maybe this is the source of the problem?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.