Forum Discussion

Magnus-CPH-DK's avatar
Magnus-CPH-DK
Helper II
4 years ago

Problems merging queries - "DataFormat.Error: We couldn't convert to Number. Details:"

Hi all,

 

I am trying to merge the queries between two tables "Fact_Kontrolregister_A_&_D" and "VirkMain" but I keep getting the following error:

 

 

This is the relationship between the two tables in my data model ( I am not sure if this is relevant in this context):

 

 

The common key is "cvrNummer" which are both of the datatype "Whole Number". Therefore I do not understand why I receive the error message since the value already ought to be a number (which the cannot be converted to number...?). I cannot simply remove the rows since there might be several rows with the same issue.

 

After googling the error message I tried using "Trim" and "Clean" in order to remove potential whitespace in both columns. This did not solve the problem.

 

Any ideas of how to solve the problem? 

Thanks in advance!

(I have attached a couple of screenshots showing my process so far)

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Magnus-CPH-DK ,

     

    Sorry, due to the size of your image, it is difficult for me to see the content, even when zoomed in.

    Maybe you can try converting the offending column to text format, and then converting it to the type you need after the merge is complete.

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Magnus-CPH-DK's avatar
      Magnus-CPH-DK
      Helper II
      4 years ago

      Hi Anonymous 
      Thanks for your reply. Unfortunately, I cannot share the PBI file, since it contains sensitive data about citizens and conracts. 
      I still havent found out why I get a data type convert error when the data is already numbers...

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    4 years ago

    I, too, am having a problem with the size of the image. Which step returns the error? You have selected the last step, but there is not type conversion going on in that step. Please step backwards in the Applied Steps window until you see which step is actually throwing the error. 

    Trim and Clean may not be enough as there may be NBSP ZWSP and other non-printing characters not affected by those functions.  But we need more information (and also visible data) in order to advise you better.

  • Magnus-CPH-DK's avatar
    Magnus-CPH-DK
    Helper II
    4 years ago

    Hi ronrsnfld  and Anonymous .
    Sorry for the long response tume, I have been away on vacation. But I still haven't found the solution.

    Are you able to see the pictures from here?


    The table names are slightly different, but the issue is the same: I want to merge the column "navn" from table "Dim_CVR" to table "Fact_Kontrolregister_A_&_D" via column "cvrNummer" which is a shared column between "Dim_CVR" and "Fact_Kontrolregister_A_&_D". "cvrNummer" is of the datatype Whole Number in both tables.

    "cvrNummer" is the shared key between the two tables and is of type Whole Number. The arrow indicates the column I would like to merge.Here I expand the merged tableThis is the convert to number error I keep getting. How come the number 30488687​​ cannot be converted to a number, when it is already a number?

    • ronrsnfld's avatar
      ronrsnfld
      Super User
      4 years ago

      Unfortunately, at this point, without sufficient data and code to reproduce the problem, I will not be able to help you further. Perhaps someone else can.  If not, perhaps you could create a file, with sensitive information removed, that will reproduce the problem (with data AND code) and upload it to some sharing site (eg OneDrive; DropBox) and then post a link here.