Help with Column Data Type Conversion

[rcgx13]

Hello, 

I am fairly new to using powerBI, so I may not use/understand all the correct terminology.  One of my data columns is organization ID and is a mix of numbers and letters, I have changed the data type to text. I am still getting a ton of mismatched data errors. Some of the data is all numbers, some a mix of numbers and letters. Specifically, I am receiving errors on IDs that start with a letter, example N2000. The error states this these IDs cannot be conerted to a number, but this error pops up after I did the data type change to text. I read that this may be becuase powerBI thinks the first letter is an identifier and not part of the string, not sure if that's what really is happening. Any advice on how to fix this? Thanks! 

[Ray_Minds]

Hi @rcgx13 

Basically, what happens is Power BI sees your column as a number at first, and then it tries to convert it to text. This usually leads to some headaches, especially if your IDs start with letters. 
 
 
1. Set the Column Data Type to Text in Power Query 
 
First things first, you need to fire up the Power Query Editor (you can find it under Transform Data in Power BI). 
 
Now, find your Organization ID column in there. 
 
Once you spot it, just click on the little dropdown menu next to the column name and choose Text. 
 
Here’s a little tip: If you’re still seeing errors afterward, go ahead and delete that pesky 'Changed Type' step from the Applied Steps pane. 
 
Don’t forget to click Close & Apply to save all your hard work! 
 
2. Look Out for Incorrect Auto-Detection 
 
Sometimes, Power BI might mistakenly think those IDs are numbers instead of text, which can mess things up. 
 
To fix this, you can use the Text.PadStart() function in Power Query. Try something like this: 
 
= Table.TransformColumns(Source, {{"Organization ID", each Text.From(_), type text}}) 
 
This will make sure that all your values are treated as text, even if they look like numbers. 

1.  
    
    
   3. Remove Errors if Necessary 
    
   If you still see errors hanging around, it's time to take action. 
    
   In Power Query, head over to "Transform" and then "Replace Errors." 
    
   You can replace those errors with a placeholder like "Unknown" or just "Error." Whatever floats your boat! 
    
   4. Check in the Data Model 
    
   After you've made those changes, swing by the Data View in Power BI. 
    
   You’ll want to ensure that all your IDs—whether they’re numbers or letters—show up as text, and, crucially, without any errors. 
   
   

   Best regards,
   Ray Minds
   http://www.rayminds.com
   https://www.linkedin.com/company/rayminds/

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

   
   
   
    
    

[Anonymous]

Thanks for the reply from saud968, please allow me to provide another insight:

Hi, @rcgx13 
Thanks for reaching out to the Microsoft fabric community forum.

Could you please let us know if h saud968‘s response resolved your issue? If it did, kindly accept it as the solution.

As saud968  mentioned, data containing characters or spaces cannot be converted to numeric types.

You can use the Text.Replace() function in Power Query to replace spaces with empty strings. Alternatively, the Text.Trim() function is also a good choice. For further details, please refer to:

Text.Trim - PowerQuery M | Microsoft Learn
Text.Replace - PowerQuery M | Microsoft Learn
 

You may need to note that if you change the data type, you need to click "Replace Current"; otherwise, the error will persist.

 For more information on changing data types, please refer to:

Data types in Power BI Desktop - Power BI | Microsoft Learn

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

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

[saud968]

Here are a few steps you can take to resolve this:

Ensure Data Type is Set Correctly:

Double-check that the data type for the column is set to Text in both the Power Query Editor and the Data View. Sometimes, changes in one view might not reflect in the other.
Remove Incompatible Characters:

If there are any hidden characters or spaces, use the TRIM function to clean up the data. This can help ensure that all entries are treated as text.
Check for Data Type Conflicts:

Sometimes, Power BI might still try to interpret certain values as numbers. You can use the FORMAT function to explicitly convert the values to text:
NewColumn = FORMAT([OrganizationID], "Text")
Use Power Query to Transform Data:

In Power Query Editor, you can use the Transform tab to change the data type to text. Additionally, you can use the Replace Errors option to handle any remaining issues.
Check for Locale Settings:

Ensure that your locale settings are consistent, as different locales might interpret data differently.
Review Error Messages:

Carefully review the error messages to identify any specific patterns or issues. This can provide clues on what might be causing the problem.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!