Forum Discussion

quyenduong's avatar
quyenduong
Helper II
3 years ago

Cannot change text to number in Power Query Editor

Hi there, 

 

I have several columns (all columns with Est & Year in the header) that include null value and number like this 190,513. Power BI automatically recognizes this column as Text but not Number. Now, I must change the data type from Text to Whole Number. Here is what I did:

 

  • In the Power Query Editor: Transform > Choose the column > Change Data Type from text to whole number 

 

Then the whole column becomes Error (screenshot below is example of one column, it will be similar when i apply the same for the other columns).

 

I read some questions in the platform but still cannot solve this problem. Thank you so much for answering. 

Best regards, 

Need Help

4 Replies

    • quyenduong's avatar
      quyenduong
      Helper II
      3 years ago

      Hi amitchandak , I found out that the data containing numbers always have "comma" between numbers, therefore, it fails to convert to Whole number.

       

      I saw that there is a function to replace value "," to nothing but I wonder if i can apply this for all columns containing number so I dont have to do it one by one. 

      Thank you

       

      • v-xiaotang's avatar
        v-xiaotang
        Community Support
        3 years ago

        Hi quyenduong 

        Thanks for reaching out to us.

        Usually a number column is automatically recognized as a number column, unless the number contains some spaces, then the column will be recognized as text type. Could you share a sample file for us to check? thanks

         

         

        Best Regards,

        Community Support Team _Tang

        If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

  • JasminParis's avatar
    JasminParis
    New Member
    2 years ago

    Bonjour,

     

    J'ai rencontré le même souci, avez-vous trouvé la solution, SVP?