How to make select a format for column containing numbers or text?

[Moh_Ashraf]

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

[BA_Pete · Accepted solution]

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

[ZhangKun]

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.

[techies]

Hi @Moh_Ashraf in power query , add a custom column 

= try Number.From([ColumnName]) otherwise [ColumnName]

[BA_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

[Moh_Ashraf]

I changed the data type to text but actually still error.

I changed the data type to text but Actually, still not working

[v-aatheeque]

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!

[Moh_Ashraf]

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".

[BA_Pete · Accepted solution]

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

[Moh_Ashraf]

Yes, thats right,

I found another step for automatic change data type at first,

I found the column that I have a combined text and value is "int64. type" but I changed it to "Type any"

Thank you for support

[BA_Pete · Accepted solution]

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