Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Cannot convert value " of type Text to type Integer

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?

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

4 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
    HotChilli
    Community Champion
    7 years ago

    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.

  • E0657198's avatar
    E0657198
    New Member
    4 years ago

    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])))