cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
twister8889
Helper V
Helper V

Error convert the number to text in a column with mixed data types

Hi guys,

 

I have a column with with mixed date types (text and number), for example:

Column1

C42890

UserName1

SD

89093

I have the error to convert the value 89093 to text.
I already tried create another column, Column2, and concatenate Column1 with character "_" like: Column1 & "_"
But the error continues, how can I solve this problem?
Thanks.

1 ACCEPTED SOLUTION

@twister8889 

 

In power query editor select the field that displays the error message --> Right Click on it --> Change datatype to 'Text'. See image below. 

 

I hope this helps

 

Change_datatype.jpg

View solution in original post

7 REPLIES 7
PhilipTreacy
Super User
Super User

Hi @twister8889 

I think what is happening is that when you first created the query, the data in the column was data type number .

At some point later the source data column has had some text entered into it, so when you refresh, the existing query step is trying to convert text to number - this would generate an error.

If the original query converted the column to text, and subsequently the column contained numbers, then Power Query would convert those numbers to text without error.

 

Can you please share some screenshots of the data, the error and the actual error message.

Plus if you can share the actual Power Query M code for the query that would be helpful.

regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi, 
The problem was that in my first extraction, I tried to do the upper case in this column, so I had the error of converting to number as @PhilipTreacy  said, so I removed this upper case step and now, I have a column with number and text. 

 

Thank you so much.

v-alq-msft
Community Support
Community Support

Hi, @twister8889 

 

A column can only have a data type. Power BI will identify the above columnn as text. So there is no need to convert it. 

 

You may also try the following calculated column to convert it to text.

Result1 = CONCATENATE([Column1],"")

Or

Result2 = [Column1]&""

 

Best Regards

Allan

 

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

 

Hi guys,

First of all, thanks for your answer....

 

However, I tried these options in power query ( m language ) but is not working yet. 
I added a new column with these alternatives.
Column = FORMAT('Table'[Column1], "string")
Result1 = CONCATENATE([Column1],"")
Result2 = [Column1]&""


@PhilipTreacy 
I import the excel file, and Column1 has the content example: 
ABCDE
890766
When I did the refresh, I have a red flag ( 1 of the loaded queries contained errors )
And the error is because power bi, tried to detect the data type for the Column, that sometimes is a string and sometimes is a number

I still have this error.

@twister8889 

 

In power query editor select the field that displays the error message --> Right Click on it --> Change datatype to 'Text'. See image below. 

 

I hope this helps

 

Change_datatype.jpg

View solution in original post

PhilipTreacy
Super User
Super User

Hi @twister8889 

A column of data like that will already be text so I don't understand what are you trying to do exactly to convert 89093 to text?  Could you explain please?

Regards

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


themistoklis
Super User
Super User

@twister8889 

Try the following formula:

Column = FORMAT('Table'[Column1], "string")

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.