Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adcraig18
New Member

Power BI Not Recognizing Non-numerical data in a "Text" column

Hello,

 

I am using an excel spreadsheet for Power BI Desktop.  I have a column in excel formatted as "TEXT" which is primarily numerical lot numbers.  However, some lot number have alphabetical characters or hyphen (such as 6341GF or 6091-1).  Rows with these lot numbers are not being recognized in Power BI and data is not being displayed.

 

I have gone to the Power Query Editor and confirmed that the column is also formatted as "Text" in Power BI, and also under the Model window I have ensured the column is formatted as text.

 

I am assuming there is something simple I'm missing.  Any ideas?

 

Thank you,

Aaron

1 ACCEPTED SOLUTION

Hello,

 

So I copied some of my data into a new excel sheet and loaded it into Power BI and there were no troubles...

 

I found my answer here: https://community.powerbi.com/t5/Desktop/Error-DataFormat-Error-We-couldn-t-convert-to-Number-Detail...

 

I went into advanced editor and made my product number type text instead of Int64.Type.  

 

Thank you!

Aaron

View solution in original post

4 REPLIES 4
kethr
New Member

Old post but I hit a similar issue.
I'm wokring with 12,000+ lines, pulling multiple sheets into one with PowerQuery and the first 1000 (or more) rows were only numbers. Even though all the columns, of all sheets, were formatted as Text and i could pull up the non-numerical values from using the Filter... PowerBi was still seeing it as numerical (forcing the Σ symbol and negating everything text).

My solution was to Sort by a different column in my powerquery so that there were non-numerical values in the first 1000. Then PowerBi (web version) picked up the change after a refresh and added the missing values.

The sorting in PQ didn't make a difference to me since it all went to Bi anyway.

Almost seems like a bug to me, but whatever. 

Maybe this will help someone down the line. 

v-rzhou-msft
Community Support
Community Support

Hi @adcraig18 

I have test by my sample but I couldn't reproduce the issue.

I build two samples, one is start by numbers like "63241" and another is start by number with alphabetical characters or hyphen like"6329-M". Both columns are in Text type in Excel. 

1.png2.png

When I upload them into Power BI Desktop, both work well.

3.png

4.png

Could you share a sample with me by your Onedrive for Business? And If I have any misunderstanding on your issue, please tell me. 

 

Best Regards,

Rico Zhou

 

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

Hello,

 

So I copied some of my data into a new excel sheet and loaded it into Power BI and there were no troubles...

 

I found my answer here: https://community.powerbi.com/t5/Desktop/Error-DataFormat-Error-We-couldn-t-convert-to-Number-Detail...

 

I went into advanced editor and made my product number type text instead of Int64.Type.  

 

Thank you!

Aaron

DataInsights
Super User
Super User

@adcraig18,

 

Would you be able to provide a link to sample data that illustrates the issue? If the first value in the Excel column is a number and you add a leading single quote to this value, it causes Power Query to treat the column as text.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.