Cannot merge because column has both text and numbers

[danhendo888]

Test.xlsx

Sheet1 Account,Account,Activity,Account,Activity 77401405,77401405,LANDTRANS,Output-->,77401405,LANDTRANS 77401405,EXPENSE_INP,77401405,LANDTRANS 77401405,REVENUE_INP,77401405,LANDTRANS 77401405,77401405,LANDTRANS 77401405,77401405,LANDTRANS EXPENSE_INP,EXPENSE_INP 77401405,77401405,LANDTRANS 77...

docs.google.com

I've brought in two tables into power query:
- AccountNumber (column has text and numbers. Some of the numbers (e.g rows 2 - 6) are formatted as text (i.e. it has the green ribbon in the cell and is aligned to the left))
- LookupTable

The AccountNumber table has many columns but I've reduced it to one column for this question.
I'm trying to use the LookupTable and do a lookup on the account number to achive the output table.

I tried merge but get an error because the AccountNumber column has both text and numbers.

Is there a way that I can achieve the output table where some columns have both text and numbers involved?

 

[danhendo888]

The LookupTable can be either of the below, whichever is easier to achieve the output table.

Account	Activity
77401405	LANDTRANS

Account	Activity
77401405	LANDTRANS
EXPENSE_INP

The row with text doesn't really need to be either in the lookup table. But there will be text in the AccountNumber table.

 

[RoryA]

Assuming the numeric values in the lookup table are actual numbers, you could add a calculated column to the main table using something like:

=try Number.From([Account]) otherwise [Account]

to convert any numeric data into an actual number, then merge on that new column.