Lookup Value from different table workaround not working

[ExcelMonke]

Due to some cardinality issues I am trying to use lookupvalue/calculate firstnonblank as a workaround.

Background:
I have two tables:
TableA:

Column1: Unique ID values (PowerBI states there are multiple same values, although I check and these seem to all be unique and not duplicate. Nulls have been removed.)

Column2: Visit Type (RPV or NPV)

TableB:

Column1: Unique ID values (can be duplicate or blank)

For whatever reason, I can't seem to connect 'TableA'[Column1] with a One:Many relationship with 'TableB'[Column1].

My ultimate goal is to have TableB have values of TableA[Column2] linked correctly. I.e. each 'TableA'[Column2] value, should be matched up with the corresponding values in 'TableA'[Column1] and 'TableB'[Column1] respectively.

I have tried the Lookupvalue function and it produced an error. I next tried the following formula:

CALCULATE(
FIRSTNONBLANK('TableA'[Column2],1),
FILTER(ALL('TableA'), 'TableA'[Column1]='TableB'[Column1])
)

However I get wildly different results (Count of ID (Column1) in Table B and corresponding category):
Result:

NPVorRPV	Count of ID
RPV	1702012
NPV	437466
	56890
Total	2196368

Intended Result (count of ID in Table A (Column1) and corresponding category):

NPVorRPV	Count of ID
RPV	1599232
NPV	364907
Total	1964139

Any help is appreciated - thanks!

[amitchandak · Accepted solution]

@ExcelMonke , Based on what I got

New column in Table 2

Minx(filter( 'TableA', 'TableA'[Column1]='TableB'[Column1]) , 'TableA'[Column2])

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8