Calculated column from one table to another

Hi  Revathi22 ,

Are you referring to going to the Tax Database table based on [Pattern] to find out if there is a matching [Ref_Mark], and if this [Pattern] is all true, then valid.

I created some data:

Customer table:

Tax Database:

Here are the steps you can follow：

1. Add two new steps with code of the following form.

= Table.ReplaceValue(#"Changed Type","","",(x,y,z)=>Text.Combine(Text.SplitAny(Text.From(x),"0123456789"),"9"),{"Taxid"})

= Table.ReplaceValue(#"Custom1","","",(x,y,z)=>Text.Combine(Text.SplitAny(Text.From(x),"ABCDEFGHIJKMLNOPQRSTUVWXYZ"),"x"),{"Taxid"})

2. Create calculated column.

Test1 =
var _column=
SELECTCOLUMNS(FILTER(ALL('Tax Database'),'Tax Database'[Country_Code]=EARLIER('Customer table'[Country_Code])),"test",'Tax Database'[Ref_Mark])
retur
CONTAINSSTRING(
    _column,'Customer table'[Taxid])

Test2 =
var _count=
COUNTX(FILTER(ALL('Customer table'),'Customer table'[Country_Code]=EARLIER('Customer table'[Country_Code])&&'Customer table'[Test1]=FALSE()),[Country_Code])
RETURN
IF(
    _count=BLANK(),"Valid","Invalid ")

3. Result:

If the above results do not meet your expectations, can you express the expected results in the form of a picture, we can help you better.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly