Forum Discussion
How to make select a format for column containing numbers or text?
I have a column contains sometime numbers and other times text, it make error "Data format error, we couldn't convert to number".
i.e:
Cell 1 > 83
Cell 2 > Open
You need to make sure you're making the type conversion BEFORE any other conversions take place. It looks like you probably have 'Automatically detect data types' enabled and Power Query has put a conversion step in for you at the start of the query steps just after the source is imported.
Go to the top of your query steps and see if there's a Changed Types step already there. If yes, then select that step, change the type of your column to Text and, when prompted, choose to replace the step, rather than add a new one.
Pete
Perfect. Glad that worked for you.
As an aside: Do NOT use Type Any in Power Query. You should always change your columns to explicit data types. It doesn't have to be done at the first step that we're talking about, but you should always ensure that all of your columns have an explicit data type by the end of the query to avoid causing yourself problems when you apply the query to the data model.
Pete
9 Replies
Hi Moh_Ashraf
You need to convert this column to Text type. If you then need to do statistaical functions/comparisons on just the numbers, you will need to use conditional elements in your calculations, something like this:
if try Value.Is(Number.From([YourColumn]), type number) otherwise false then [YourColumn] * 10 else [column] // or null or whatever you want your escape value to bePete
I changed the data type to text but actually still error.
I changed the data type to text but Actually, still not working
Hi Moh_Ashraf ,
Thanks for reaching out to Microsoft Fabric Community forum.
Based on the screenshot i have done repro from end , i can able to change the data type as expected.
Once you have loaded the data , change the data type to "Text" as per the below screenshot:
Then Replace vlaues with reuqired data as shown in below :
The expected result as here :Note : Ensure Data Type Change is Applied Properly.
If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.
If you continue to face issues, feel free to reach out to us for further assistance!
Hi Moh_Ashraf in power query , add a custom column
= try Number.From([ColumnName]) otherwise [ColumnName]
You need to check if the previous step set the data type of this column to numeric type.
When you use the connector to read data from Excel file, it may guess your data type. If you want to keep all the content, you should manually set it to text type.
