Cannot convert value '' of type Text to type Integer

[Aefuentes]

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,

[PijushRoy]

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

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

[PijushRoy]

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. 

[Aefuentes]

Hey,

I need to get the average of this column ignoring blanks.

Customer Satisfaction

10. Very Happy

10. Very Happy

7

8

10. Very Happy

9

[PijushRoy]

Hi @Aefuentes 

Please use replace value ". Very Happy" to blank like below screenshot

Then change datatype and calculate average

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

[Aefuentes]

I tried but didn't work.

The value is "10.Very Happy" not "10. Very Happy" (my bad).

Anyways, still the same after the replacement.

[PijushRoy]

Hi @Aefuentes 

I am not able to reproduce the issue, please check Power Query other steps and use the replace before you are using if LEN
Find pbix
https://drive.google.com/file/d/1gnQJ9kmGYeGA9jFQ4BosMqHF71niY4yd/view?usp=sharing

[Aefuentes]

Hey,

I made the .pbix  with the real info.

https://drive.google.com/file/d/1VOy1UXcHNfz9UEbwWOo2t63Wi_mZW1-k/view?usp=sharing

Please give it a try

[PijushRoy]

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]

Hey,

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