Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
in Power BI by 23 26 40

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?

1 Answer

0 like 0 dislike
by 23 26 40
edited by

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.

change data type

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 .
    Create a custom column in Power Query

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

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

    Number.ToText formula in Power Query
    > Number.ToText converts number to text.

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

    Merge two tables

See Also

If you don’t ask, the answer is always NO!