Calculated column from one table to another

[Revathi22]

I have 2 data model

1. Customer and Tax_Database.
2. In Customer, we have country code, party type & taxid columns
3. In Tax_Database, we have Country code & Ref_mask columns

The ref mask will have many patterns.. i have provided few scenarios.. please note we have many scenarios , i have provided only few examples

Country_Code Ref_Mask
AF 99999999999
AL X99999999X
AM 99999999
AO 9999999999
AU 99 999 999 999
AZ 9999999999
BA 999999999999
BD 999999999-9999
BF 99999999X
BG 9999999999999 | XX9999999999999
BR 99999999999999
BW XX99999999999 | XX99999999999
CL 99999999-9 | 99999999-X
CO 9999999999 | 9999999999
CZ CZE999999999999999 | CZ9999999999

1. 9 means it will have any digits between (0 to 9)
2. X means it will have any letters between (A to Z)
3. Apart from X if we have any letter , it means that specific letter has to be fixed. eg.. apart from X if any letter like CZ or ABC contains in the database then that respective letters should remains same.

I need a calculated column as per the below logic.

1. It should check the tax database based on each country and check whether the pattern met in the customer table under taxid column.

2.If any country has multiple pattern it should check all the pattern if any one pattern met then it is valid, else invalid.

3. If the specific country has only one pattern then the condition should check only that pattern,if met valid else it is invalid.

4. If the customer table has Natural person in the party type then mention is as Dont consider.

5. If the customer table has blank in the taxid column then mentioned it as Dont consider.

Please assist.

Tax Database snapshot:

Customer table snapshot:

[Anonymous · Accepted solution]

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

[Revathi22]

Thank you so much for your swift response.. But this is not my requiremnent. 🙂

Eg.

If Customer table has

AE - 345678924 in Taxid column

If Taxid_database table has

AE - 999999999 in Ref_Mask then it is Valid since it meets the pattern

But If Taxid database has a ref_mask as

AE - XX99999999 then it is Invalid 

Same like above it should check automatically for all the countries and should provide the result for each records in the Customer table whether the details in TaxID column is valid or not. 

My another challenge is some countries has multiple pattern wherein the condition needs to check each pattern and if any one met then it is Valid else Invalid.

[Anonymous]

Hi  @Revathi22 ,

Here are the steps you can follow：

1. In Power Query -- Add a new step in the applied steps.

2. 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"),Table.ColumnNames(#"Changed Type"))

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

3. Create calculated column.

Ref_Mark =
CONCATENATEX(
    FILTER('Customer table',
    'Customer table'[Country_Code]=EARLIER('Tax Database'[Country_Code])),
    [TaxID]," | ")

4. Result:

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