Lookup the value from two tables and Append the other table along with the lookup value

[arvindarvind24]

Hi All, Need your help on Lookup the value from two tables and Appending the other table along with the lookup value.

Table 01			Table 02			Table 03
Code	Name	Type	Code	Name	Type	Code	Name	Type
1	AA		11	AA	Y	21	NN	Y
2	AB		12	AB	Y	22	NB	Y
3	AC		13	AC	Y	23	NC	Y
4	AD		14	AD	Y	24	ND	Y
5	AR		15	UR	Y	25	NR	Y
6	AA		16	UU	Y
7	AB		17	UB	Y

1st Output: lookup the matching value from Table 02 and result "N" for matching value and "Y" for not matching value.

Table 01
Code	Name	Type
1	AA	N
2	AB	N
3	AC	N
4	AD	N
5	AR	Y
6	AA	Y
7	AB	Y

2nd Output: To append all three tables With the 1st output value.

Output Table
Code	Name	Type
1	AA	N
2	AB	N
3	AC	N
4	AD	N
5	AR	Y
6	AA	Y
7	AB	Y
11	AA	Y
12	AB	Y
13	AC	Y
14	AD	Y
15	UR	Y
16	UU	Y
17	UB	Y
21	NN	Y
22	NB	Y
23	NC	Y
24	ND	Y
25	NR	Y

@amitchauhan @bvanderwatt @ray_ux @tamerj1 @SpartaBI @v-chenyue-msft 

[arvindarvind24]

HI @tamerj1 

Sorry for that, Please find the new table details mentioned below.

Table 01			Table 02			Table 03
Code	Name	Type	Code	Name	Type	Code	Name	Type
1	AA		11	AA	Y	21	NN	Y
2	AB		12	AB	Y	22	NB	Y
3	AC		13	AC	Y	23	NC	Y
4	AD		14	AD	Y	24	ND	Y
5	AR		15	UR	Y	25	NR	Y
6	AW		16	UU	Y
7	AC		17	UB	Y

[tamerj1 · Accepted solution]

Hi @arvindarvind24 
Is this what you want? Please refer to attched file

Type = 
IF (
    ISBLANK (
        LOOKUPVALUE ( 
            Table2[Type],
            Table2[Name],
            Table1[Name]
        )
    ),
    "Y",
    "N"
)

Table = 
FILTER (
    UNION (
        Table1, 
        Table2,
        Table3
    ),
    [Type] = "Y"
)

[arvindarvind24]

@tamerj1 Thank you 😊

[arvindarvind24]

Hi @tamerj1 ,

  Thank you for the reply.

It's working and can we filter only the "Y" value into the new table?

[tamerj1]

@arvindarvind24 

The solution is updated in the original reply

[tamerj1]

Hi @arvindarvind24 
Why Code 6 - AA and Code 7 - AB are considred not matching?