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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

9 REPLIES 9
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

I have same issue mentioned above where I have values of mixed data type of letters and numbers; like Area "B08". Values like B08 has shown Error, I tried changing column type to Text but it didn't work and Error is still there.

Yalmousa_0-1690374639538.png

 

If you are extracting from a folder check the "transform data sample" if this is set to type number etc then it will keep throwing the error even if you change type in the Applied Steps to text. Hope this helps someone. 

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
Community Champion
Community Champion

@twister8889 

Try the following formula:

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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