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_
CNENFRNL
3 years agoCommunity Champion
Hi, my friends, it seems that I'm always late 😅
For this question, I personally prefer PQ or SQL to DAX, if possible,
let
T1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEYUOlWB3cXCOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [row = _t, cat = _t, subCat = _t]),
T2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoE4iIQw9DAQClWB03QCEkwCSZoDBKMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [row = _t, cat = _t, region = _t, value = _t]),
Leftjoined = Table.NestedJoin(Table.Distinct(Table.RemoveColumns(T2, "value")), {"row","cat"}, Table.Distinct(T1), {"row","cat"}, "lj", JoinKind.LeftOuter),
#"Expanded lj" = Table.ExpandTableColumn(Leftjoined, "lj", {"subCat"}, {"subCat"})
in
#"Expanded lj"
SELECT LT.*, RT.SUBCAT
FROM
(SELECT DISTINCT ROW, CAT, REGION FROM T2) LT
LEFT JOIN (SELECT DISTINCT ROW, CAT, SUBCAT FROM T2) RT
ON LT.ROW = RT.ROW AND LT.CAT = RT.CAT
smpa01
3 years agoCommunity Champion
Thanks, Truly seond that
SQL>DAX>.......>PQ
, but DAX (SSAS server) is the only option as SQL is out of scope and PQ is not an option due to performance.
- CNENFRNL3 years agoCommunity Champion
Table = GENERATEALL( ALLEXCEPT( T2, T2[value] ), CALCULATETABLE( VALUES( T1[subCat] ), TREATAS( CALCULATETABLE( SUMMARIZE( T2, T2[row], T2[cat] ) ), T1[row], T1[cat] ) ) )