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

[twister8889]

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.

[PhilipTreacy]

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

[twister8889]

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]

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.

[twister8889]

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.

[themistoklis · 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

[Yalmousa]

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.

[TcWolf]

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]

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.

[themistoklis]

@twister8889 

Try the following formula:

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