Join Two Tables

[amay15]

Sl no	Item	Code		Sl no	Parent Item	Child Item		Sl no	Program	Sub Program	Project
1	A	Program		1	A	B		1	A	B	D
2	B	Sub program		2	B	D		2	C	Null	E
3	C	Program		3	A	D			Result	Table
4	D	Project		4	C	E
5	E	Project		Table	2
	Table	1

[v-cherch-msft]

Hi @amay15

It seems you may use 'pivot columns' and 'merge queries' in Query Editor to achieve that.

Regards,

Cherie

[PattemManohar]

@amay15 I've tried to solve this in DAX as below:

Please add below two columns (supporting columns) in Table 2 as below

Exclude = 
VAR _Flag1 = LOOKUPVALUE(Test136ParentChild[Parent],Test136ParentChild[Parent],Test136ParentChild[Child])
VAR _Flag2 = LOOKUPVALUE(Test136ParentChild[Child],Test136ParentChild[Child],Test136ParentChild[Parent])
RETURN IF(Test136ParentChild[Parent]=_Flag1 || Test136ParentChild[Parent]=_Flag2,"Y","N")

Child1 = LOOKUPVALUE(Test136ParentChild[Parent],Test136ParentChild[Parent],Test136ParentChild[Child])

Then, Please try this as a "New Table"

Test136Out = 
VAR _Temp = ADDCOLUMNS( 
                        CALCULATETABLE(Test136ParentChild,Test136ParentChild[Exclude]<>"Y")
                        ,"Program",Test136ParentChild[Parent]
                        ,"SubProgram",IF(Test136ParentChild[Child]=Test136ParentChild[Child1],Test136ParentChild[Child])
                        ,"Project",IF(Test136ParentChild[Child1]=BLANK(),Test136ParentChild[Child])
                      )

RETURN SELECTCOLUMNS(_Temp,"Program",[Program],"SubProgram",[SubProgram],"Project",[Project])