Join Two Tables

Question

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Sl noItemCode&nbsp;Sl noParent ItemChild Item&nbsp;Sl noProgramSub ProgramProject1AProgram&nbsp;1AB&nbsp;1ABD2BSub program&nbsp;2BD&nbsp;2CNullE3CProgram&nbsp;3AD&nbsp;&nbsp;&nbsp;Result&nbsp;&nbsp;Table&nbsp;4DProject&nbsp;&nbsp;&nbsp;4&nbsp;C&nbsp;E&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5EProject&nbsp;&nbsp;&nbsp;Table2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

pattemmanohar · Answer

amay15&nbsp;I've tried to solve this in DAX as below:&nbsp;Please add below two columns (supporting columns) in Table 2 as below&nbsp;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])&nbsp;Then, Please try this as a "New Table"&nbsp;Test136Out = 
VAR _Temp = ADDCOLUMNS( 
                        CALCULATETABLE(Test136ParentChild,Test136ParentChild[Exclude]&lt;&gt;"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])  &nbsp;

v-cherch-msft · Answer

Hi&nbsp;amay15&nbsp;It seems you may use 'pivot columns' and 'merge queries' in Query Editor to achieve that.Regards,Cherie

Forum Discussion

Join Two Tables

2 Replies

Related content

Joining 3 Tables (4 with DimDate)

Join the community

conditional join

PowerBI - table join question

Spatial Join


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