Forum Discussion
smpa01
3 years agoCommunity Champion
Appending issue
I have two base tables as this
//t1
| row | cat | subCat |
|-----|-----|--------|
| 1 | a | a1 |
| 1 | a | a1 |
| 1 | a | a2 |
//t2
| row | cat | region | value |
|--...
- 3 years ago
Try unioning left and right outer joins to get a full outer join.
Table = VAR _t1_ = SELECTCOLUMNS ( t1, "row", t1[row] + 0, "cat", t1[cat] & "", "subcat", t1[subCat] ) VAR _t2_ = SELECTCOLUMNS ( t2, "row", t2[row] + 0, "cat", t2[cat] & "", "region", t2[region] ) RETURN DISTINCT ( UNION ( SELECTCOLUMNS ( NATURALLEFTOUTERJOIN ( _t1_, _t2_ ), [row], [cat], [subcat], [region] ), SELECTCOLUMNS ( NATURALLEFTOUTERJOIN ( _t2_, _t1_ ), [row], [cat], [subcat], [region] ) ) )Technical notes:
- The + 0 and & "" are added to break data lineage so NATURALLEFTOUTERJOIN can work. You can read about this here and here.
- The SELECTCOLUMNS inside the UNION is just there to get the column in the same order for both.
- Power BI Desktop will give you angry red squiggly lines under some of the column references inside SELECTCOLUMNS but "the Name argument can be skipped if the correspondent Expression argument is a simple column reference of the iterated table".
Here is a less-compact version of the same calculation:
Table = VAR _t1_ = SELECTCOLUMNS ( t1, "row", t1[row] + 0, "cat", t1[cat] & "", "subcat", t1[subCat] ) VAR _t2_ = SELECTCOLUMNS ( t2, "row", t2[row] + 0, "cat", t2[cat] & "", "region", t2[region] ) VAR _LeftJoin_ = SELECTCOLUMNS ( NATURALLEFTOUTERJOIN ( _t1_, _t2_ ), "row", [row], "cat", [cat], "subCat", [subCat], "region", [region] ) VAR _RightJoin_ = SELECTCOLUMNS ( NATURALLEFTOUTERJOIN ( _t2_, _t1_ ), "row", [row], "cat", [cat], "subCat", [subCat], "region", [region] ) VAR _DistinctUnion_ = DISTINCT ( UNION ( _LeftJoin_, _RightJoin_ ) ) RETURN _DistinctUnion_
AlexisOlson
3 years agoSuper User
Try unioning left and right outer joins to get a full outer join.
Table =
VAR _t1_ = SELECTCOLUMNS ( t1, "row", t1[row] + 0, "cat", t1[cat] & "", "subcat", t1[subCat] )
VAR _t2_ = SELECTCOLUMNS ( t2, "row", t2[row] + 0, "cat", t2[cat] & "", "region", t2[region] )
RETURN
DISTINCT (
UNION (
SELECTCOLUMNS ( NATURALLEFTOUTERJOIN ( _t1_, _t2_ ), [row], [cat], [subcat], [region] ),
SELECTCOLUMNS ( NATURALLEFTOUTERJOIN ( _t2_, _t1_ ), [row], [cat], [subcat], [region] )
)
)
Technical notes:
- The + 0 and & "" are added to break data lineage so NATURALLEFTOUTERJOIN can work. You can read about this here and here.
- The SELECTCOLUMNS inside the UNION is just there to get the column in the same order for both.
- Power BI Desktop will give you angry red squiggly lines under some of the column references inside SELECTCOLUMNS but "the Name argument can be skipped if the correspondent Expression argument is a simple column reference of the iterated table".
Here is a less-compact version of the same calculation:
Table =
VAR _t1_ =
SELECTCOLUMNS (
t1,
"row", t1[row] + 0,
"cat", t1[cat] & "",
"subcat", t1[subCat]
)
VAR _t2_ =
SELECTCOLUMNS (
t2,
"row", t2[row] + 0,
"cat", t2[cat] & "",
"region", t2[region]
)
VAR _LeftJoin_ =
SELECTCOLUMNS (
NATURALLEFTOUTERJOIN ( _t1_, _t2_ ),
"row", [row],
"cat", [cat],
"subCat", [subCat],
"region", [region]
)
VAR _RightJoin_ =
SELECTCOLUMNS (
NATURALLEFTOUTERJOIN ( _t2_, _t1_ ),
"row", [row],
"cat", [cat],
"subCat", [subCat],
"region", [region]
)
VAR _DistinctUnion_ =
DISTINCT (
UNION ( _LeftJoin_, _RightJoin_ )
)
RETURN
_DistinctUnion_