Error when merging tables only for inner join

[Anonymous]

Hi All,

Hoping for some guidance here. I am trying to merge 2 tables together in Power Query using a text field to do the join. When I do an inner join I get an error saying "DataFormat.Error: We couldn't conver to Number. Details: 6/27/2020". No where in either table am I trying to convert a date field to a number so I have no idea where this is coming from. Additionally when I do a left or right join with the same tables I don't get an error.....

Has anyone run into a similar issue. 

Thanks

[v-diye-msft]

Hi @Anonymous 

Kindly change the data type to text. before you do the inner join

[Anonymous]

I already did that in a previous step..

[jthomson]

I'd double check that after the join it's not tried to put in an automatic changed type row first

[Anonymous]

Any guidance on how I can do that? 

When I do to look in the advanced editor for the new query after the merge this is all I can see..

let
Source = Table.NestedJoin(COMBINED_SALES_DATA, {"Commission Customer Number"}, #"Customer Number to ED Agent", {"Customer Number"}, "Customer Number to ED Agent", JoinKind.Inner)
in
Source

and output of the query just shows this