DataFormat.Error: We couldnt convert to Number

[Thamos]

Hello

When i try to make a power query connection and i delete the top 2 rows and make the third row the headlines of the collumns, it makes errors in above 700 errors in project collumn out of about 900 rows.. How come this happens? When i dont make any changes in power query as i try to transfer the data it gives me almost 80 errors. some might happen in other locations than the project collumn. its just that those collumns dont matter in order for me to do what i want to do with the data.

Im guessing its because it doesnt understand the 112(-) part. but how do i make it understand it ?
i have tried for several hours and im about to give up.
Help will be appreciated alot!

Thank u for your time.! kind regards
Thomas Steffensen

 

[alansidman]

If you are looking to convert 112-190823-5 to a number, you will need to replace the "-" by removing it. While it is there, PQ recognizes this as text that cannot be converted. Highlight the column and select the Transform tab and select Replace Values. In the first box put the dash and leave the second box blank. This will result in 1121908235 as a result and may now be converted if PQ does not do it automatically.

 

[Thamos]

Thank you, im gonna try this out.
The problem is pretty much that the product number is generated on behalf of the person that creates it, so there are a few diffrent ways its writen down the line. some do it with - between, while others have letters and some with other special signs. any there a way to make it all be readable for excel. im not really going to use the number for anything but the visual, making it possible for the individual to see what project they have pressed in to.

Kind regards

 

[RoryA]

Then I can't see any reason you can't just leave it as text.

 

[Thamos]

Then I can't see any reason you can't just leave it as text.

As shown on one of the pictures it is left as text, i just get error messages from the cells where there is a - or text. which i dont understand. is there any way to just make it understand any type of text, number, special signs and so on?

 

[RoryA]

I suspect you may be looking at the wrong step. The error is to do with converting to Number, not to Text.