Forum Discussion

valcat27's avatar
valcat27
Icon for Helper III rankHelper III
5 years ago
Solved

Error converting a text column (that contains only numbers) to number type

Hello all, 

 

I am importing a csv file with 5 columns and one of them has only decimal numbers between 0 and 1 but power BI import it as text type.  I need it as number type, but when  try to convert it to number type, I get the error: DataFormat.Error: We couldn't convert to Number.

I thought it was due the first line that contains the headers, but even after I select "Use First Row As Headers" and try to convert it to number type, I get the error.  

This is how the column looks like with the first line as headers:

I also tried to change the column name to a number, before importing, or round the values to two decimal places, but it does not help.

Can anyone help me importing/converting it to number type?

 

Thanks in advanced.

Need Help
  • valcat27's avatar
    valcat27
    5 years ago

    I did not find the problem but I tried to save my file in xlsx format and import it to power BI and it works, the column is recognized as number type. 

6 Replies

  • valcat27's avatar
    valcat27
    Icon for Helper III rankHelper III
    5 years ago

    I did not find the problem but I tried to save my file in xlsx format and import it to power BI and it works, the column is recognized as number type. 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    valcat27 Try doing a clean and a trim operation on it to remove any odd characters or trailing spaces. Also try filtering out blanks/nulls. How many rows are there total?

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    Try to remove non-printing characters by doing a Trim on that column (on transform tab under Format).

    Pat

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    the leading and trailing spaces doesn't disturb usually.

    perhaps could be some problem with you local enveronment.

    try this and let we know what appens:

     

     

     Table.SplitColumn(Origine, "Colonna1", Splitter.SplitTextByCharacterTransition({"0".."9","."}, (c) => not List.Contains({"0".."9","."}, c)), {"Colonna1.1", "Colonna1.2"})

     

    anhoter last think: try this fo onle the first few rows: you could have some rows at the bottom of table wich idoes'nt look like as you aspect.

     

    • valcat27's avatar
      valcat27
      Icon for Helper III rankHelper III
      5 years ago

      Hello Anonymous ,

       

      Thank you for your answer. 

      Can you explain me what this code is for?

      I copied it to my advanced editor substituting "Origine" by the last step name there and "Colonna1" by "CS". Is it correct? 

      This was the result:

      After that I try to convert "Colonna1.1", but I got the error. 

  • valcat27's avatar
    valcat27
    Icon for Helper III rankHelper III
    5 years ago

    Hello Greg_Deckler  and mahoneypat ,

     

    Thank you for your answers. 

    I tried the clean and trim operation and then to convert to decimal number, but I got the same error. 

    Also, I do not have blanks/nulls values and the number of rows can vary a lot because I need to import many files with this structure. I can have less than 100 rows or more than 500 rows, but I cannot tell you the maximum that I will have.