Text cut off in Power Query Editor when imported from Excel

[MikePowerBI]

Hi there,

I am having an issue where text that exists in an Excel file (used as a data source) has parts of the text cut off in the Power Query Editor window. Specifically, I am referencing the "Comments" column in the screenshots below. Any help is greatly appreciated! 

Excel source:

Powre Query Editor:

[VahidDM]

Hi @MikePowerBI 

Did you merged some cells to one cell?
Is it possible to add a column with Trim() formula in Excel and import that?

The limitation for the number of characters in Power BI is 32766 characters of data in a column of TEXT Data Type.

Check this:
https://blog.crossjoin.co.uk/2019/05/17/maximum-length-text-value-power-bi/

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[MikePowerBI]

Hi @VahidDM 

Thank you for your prompt reply!

I tried using both the TRIM and CLEAN formulas in Excel before importing the data into the Power Query editor, but I still received the same error. Additionally, I used the LEN formula to count the number of characters and it appears there are about 750 characters in the cell. 

In the Power Query editor, I counted the number of characters in each "Comments" column and it appears that only 225 characters are being imported. Do you know why this would be?

[MikePowerBI · Accepted solution]

I was able to troubleshoot some more and found that the data source was saved as an Excel workbook using the version from 1990-2003. When saved as the modern version of Excel, this resolved the issue.