Join tables using Power Query problem

[Anonymous]

Hello, I have a problem when I try to join two tables like an INNER join using Power Query. Indeed I have this error:
DataFormat.Error : We couldn't convert to Number Details : MA0302
Can someone help me with this issue ?
If you need data, I can show you.

[v-eachen-msft]

Hi @Anonymous ,

You could split the column as digit column and non-digit column. Then remove the non-digit column.

[Anonymous]

are you sure that in some of yours columns there isn't the value MA0302?

may be in a rows after the first 1000 rows.

how many rows has the table?

[Anonymous]

@Anonymous @v-eachen-msft @nandic Indeed the problem is about these rows with non binar data.
Nevertheless, they are not dirty data and I must keep them. Is there a way to join these tables without removing these rows with non binar data ?

[nandic]

@Anonymous ,
I think you already mentioed you tried and it didn't work, but converting both columns (from both tables) to text should work.
Main thing is that column types are the same.

In my demo table for join which had only one column of data type = whole number, i added copy of that column and added extra row "MA0203" and set colum data type = text.
My main table has also column of data type = whole number and join works. (example: 20200101)
If i try to connect same column from main table to new column in demo table which is text i can't create join because i receive notification "Select columns of the same type (number, text, etc) to continue).
If i create new column in main table where i have same data and also add row with value "MA0203" (column converts to data type ABC 123) and try to connect to new column in demo table, which text, all works good.

So just try to make sure column types are same in both columns are try to refresh. Try "playing" with data type in Power Query to see if it works.

[nandic]

@Anonymous , check if columns that are used for join between tables are the same data type.
It seems first table is using number and second table is using non-numeric value (maybe it is number 1,2,3..., but check in upper left corner of the cell if it is ABC, ABC 123... is should be 123).

[Anonymous]

@nandic thank for your answer !
In both tables, the column (EBELN) used to join the tables is an integer like this

And it still doesn't work. I also tried to change the type of this column in both tables to TEXT, but it shows me the same error as before

[nandic]

@Anonymous , check this value as @Anonymous  replied.
It seems as these are numeric columns, but they still contain this dirty data (null, MA0302).
I replicated the issue, if i type "text" under numeric column, join based on that column will fail and in results of error it will say that issue is value "text". So go ahead and find that "MA0302" value 🙂