Errors in column due to incorrect data type

[sarah2_williams]

Hi guys,

I have some columns in my data that are a mix of text and number, (it's ages so I have, "17 and under" then 18, 19, 20, 21 up to "75 and over"

When i've imported the data it has converted the column to whole number, which means "17 and under" and "75 and older" are showing as an error "Can't convert to number", i've transformed the colum to text, but it still shows these are error. 

Am I going wrong somewhere? should I be doing something else to transform this into Text? I have transformed and refreshed the data but it's still showing those values as error.

Thanks! 

[Pragati11 · Accepted solution]

Hi @sarah2_williams ,

I would say give this column a datatype = TEXT just after the data is loaded in Power BI.

Power BI assigns a datatype to a column by default based on the Top 200 rows it sees in the data.

1. Go to Power Query Editor
2. Under Applied Steps for this data you will see the first step is SOURCE
3. Just after this SOURCE step add a new step with datatype chnage for your column as TEXT

This will resolve your issue.

Thanks,

Pragati

[Anonymous]

@Pragati11 I have a particular case where Power Query makes the conversion automatically after expanding a table, so it does not work for me when I change the Column Type to Text, the Error is mantained:

Here is the error message:

The real value is something like: 52689-P-7K.

Hope someone who cal help is around.

Regards!

[Pragati11]

Hi @Anonymous ,

Can you please share the screenshot of Applied Steps for this query of your's ?

[Anonymous]

@Pragati11Many thanks for your willingness to help. I have already solved the issue by editing the query "Transform Files," the one that is automatically created when a record is expanded, and changing the column type to the correct one there.