Cannot convert value " of type Text to type Integer

[Anonymous]

I'm importing data into Power BI and I have "Duration" column which stores duration in seconds. I want to convert it to minutes so I've created a new column and used the following function:

ROUNDUP (mytable[Duration]/60,0)

This function works in my other report. However, it doesn't work in my new report: I'm getting "Cannot convert value " of type Text to type Integer" error. I suspect that this is due to some blank data in Duration column.

I've tried removing blank data by using filtering in power query: power query >> click on Duration table >> filter >> remove empty.

I've also tried to replace blank data: power query>> transform >> replace values >> find "blank" and "null" >> replace with 0. Both methods don't work and I'm still getting error message.

Any ideas, please?

[E0657198]

Hi,

I am also facing this issue, Any help is greatly appreciated! 

Mileage = IF(CALCULATE(FIRSTNONBLANK('Requested_VIN List'[Mileage],1),FILTER(ALL('Requested_VIN List'),'Requested_VIN List'[VIN]='Fleet Ref_VIN_List'[VIN]))="","-",CALCULATE(FIRSTNONBLANK('Requested_VIN List'[Mileage],1),FILTER(ALL('Requested_VIN List'),'Requested_VIN List'[VIN] ='Fleet Ref_VIN_List'[VIN])))

[v-lili6-msft · Accepted solution]

HI, @Anonymous 

That must be mytable[Duration] is a text column, means there must be some text format value in it.

Please change the data type of this column into number as below:

Then you filter which row is error.

Best Regards,

Lin

[Anonymous]

Thanks for your tip @v-lili6-msft! I've changed the data type to decimal number in power query editor and I didn't get any errors but I had some blanks, which I removed in filter >> remove empty.

My new column is now working as expected.

[HotChilli]

You could use the column quality and distribution features under the View menu in Power Query to have a look at your data.  You should be able to find the dodgy values that way.