Forum Discussion

Revathi22's avatar
Revathi22
New Member
2 years ago
Solved

Calculated column from one table to another

  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...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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