How to merge two tables in Power Query Editor

Question

I have two tables and I want to merge them into a single table to get all from the first table and the matched from the second table but the keys in both tables are mismatched and don't have the same datatype so I got "select columns of the same type to continue"!

Is there any workaround to merge two tables with different datatypes?

Answer 1

This problem occurs because the column in the first table has a different datatype (Text) that is not the same as the datatype of the corresponding column (Number) in the second table! So I just changed the datatype of the second column to text.

But the matched row is now ZERO because the first column appends an additional fixed text at the beginning of each number, so I have solved this problem as the following:

1. Add a new column in the first table.

2. Go to Query Editor and click on Add column Tab > select Custom Column .
   

3. Add the name of a column, add the custom formula.

     "tt"&Number.ToText([IMDB ID])
   

   
   > Number.ToText converts number to text.

4. Now the error is gone and I can merge two tables

   

See Also

• Number to text