Forum Discussion
How to make select a format for column containing numbers or text?
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
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 be
Pete
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!
I doesn't want to replace value, I need to keep the data as it is even if number or text.
What I meen is I didn't want to make calculations for number cells, I want power BI read the number as a text.
But when I change the column data type to Text, still give the same error in cells that have text "We couldn't convert to number".
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
