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

[Magnus-CPH-DK]

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)

[Magnus-CPH-DK]

Hi @ronrsnfld  and @v-stephen-msft .
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]

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.

[ronrsnfld]

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.

[v-stephen-msft]

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]

Hi @v-stephen-msft 
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...