Forum Discussion

Aefuentes's avatar
Aefuentes
Regular Visitor
2 years ago

Cannot convert value '' of type Text to type Integer

Hi guys,

 

I'm working on a project and had this issue.

 

I have a column with ABC123 Format containing values like "10. Very Happy" and others like 8, 9. I tried to clean the "10. Very Happy" values to just 10 using IF and LEN and it worked but the new column is TEXT so AVERAGE function doesn't work.

 

Tried to change the format from text to number but got this message

 

 

Hope you can help me.

 

Regards,

 

Need Help

10 Replies

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi Aefuentes 

     

    There are some extra spaces are there, please clean them when using LEN or IF function
    Please right click on the column > Transform > and use Trim option to remove extra space

     

     


    Let me know if that works for you


    If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

    Thanks
    Pijush
    Linkedin

    • Aefuentes's avatar
      Aefuentes
      Regular Visitor
      2 years ago

      Hey,

      I created a new column using TRIM function but didn't work. It still goes

       

       

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Aefuentes 

    Please right click on the column > Transform > and use Trim option to remove extra space in POWER QUERY

     

    If your requirement is solved, please make THIS ANSWER a SOLUTION

    • Aefuentes's avatar
      Aefuentes
      Regular Visitor
      2 years ago

      For some reason the origin column dont show the button "transform". The others do.

      • PijushRoy's avatar
        PijushRoy
        Icon for Community Champion rankCommunity Champion
        2 years ago

        Hi Aefuentes 

        Share sample of data in excel format
        or 
        Please create a pbix that contains sample data and Upload the pbix to OneDrive, Google Drive, or dropbox and share the link in this thread. If you are using a spreadsheet to create/import the sample data instead of the manual import method, share the spreadsheet as well.