Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Merging query faced "ole db or odbc error dataformat.error we couldn't convert to number"

hi all,

I am experiencing this error and my report failed to refresh.

 

Background:

I have two queries..

I am using "Merge Queries" function to fetch in two columns from another quaries using a common custom key.

The moment when I click the "expand" button in query editor and program try to apply the changes to the query, i encountered the error.

 

The following are the custom keys (consists of 5 field columns) which I concatenated and created in both of the query tables

      Text.Combine({[#"Pers.No."],[#"Plate no."],[CoCd],[Equipment],[EndOfMonth]})

P.S. the types of all the field columns are Text type.

 

Please note when I create merge step, there is no error when program try to apply the changes to the query. It is only when I clicks  the Expland button, the program hits error.

 

Thanks for clue.

Tuff

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    Do you mean the merge two queries as following? it works fine on my side. What the type of data source and connective mode you are using?

     

     

    Please also refer to the similar therd: https://community.powerbi.com/t5/Desktop/Error-returned-OLE-DB-or-ODBC-error-DataFormat-Error-We-couldnt/td-p/486357


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Do you mean the merge two queries as following? it works fine on my side. What the type of data source and connective mode you are using?

     

     

    Please also refer to the similar therd: https://community.powerbi.com/t5/Desktop/Error-returned-OLE-DB-or-ODBC-error-DataFormat-Error-We-couldnt/td-p/486357


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.