Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
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
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
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.
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.
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
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.
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.
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.
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
67 | |
49 |
User | Count |
---|---|
139 | |
113 | |
104 | |
64 | |
60 |