Appending issue

[smpa01]

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 |
|-----|-----|--------|-------|
| 1   | a   | r1     | 100   |
| 1   | a   | r1     | 200   |
| 1   | b   | r1     | 300   |

I need a to build a table that has all the unique combinations of row,cat,t1.subCat and t2.region, like this

| row | cat | subCat | region |
|-----|-----|--------|--------|
| 1   | a   | a1     | r1     |
| 1   | a   | a2     | r1     |
| 1   | b   |        | r1     |

I am currently achieving it by follwoing

Table = 
/*VAR x =
    UNION (
        SUMMARIZE ( t1, t1[row], t1[cat], t1[subCat] ),
        SUMMARIZE ( t2, t2[row], t2[cat], t2[region] )
    )*/
VAR y1 =
    DISTINCT (
        UNION (
            SUMMARIZECOLUMNS ( t1[row], t1[cat] ),
            SUMMARIZECOLUMNS ( t2[row], t2[cat] )
        )
    )    
VAR y2 =
    GENERATEALL (
        GENERATEALL (
            y1,
            VAR _keyFirst = [row]
            VAR _keySecond = [cat]
            RETURN
                SELECTCOLUMNS (
                    FILTER (
                        SUMMARIZE ( t1, t1[row], t1[cat], t1[subCat] ),
                        [row] = _keyFirst
                            && [cat] = _keySecond
                    ),
                    "subCat", [subCat]
                )
        ),
        VAR _keyFirst = [row]
        VAR _keySecond = [cat]
        RETURN
            SELECTCOLUMNS (
                FILTER (
                    SUMMARIZE ( t2, t2[row], t2[cat], t2[region] ),
                    [row] = _keyFirst
                        && [cat] = _keySecond
                ),
                "region", [region]
            )
    )
RETURN
    y2

I am performing this over a large table. Is there a better/optimized way to achieve the same

@AlexisOlson @CNENFRNL 

Thank you in advance

[CNENFRNL]

@smpa01 @AlexisOlson 

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]

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.

[CNENFRNL]

Table =
GENERATEALL(
    ALLEXCEPT( T2, T2[value] ),
    CALCULATETABLE(
        VALUES( T1[subCat] ),
        TREATAS(
            CALCULATETABLE( SUMMARIZE( T2, T2[row], T2[cat] ) ),
            T1[row],
            T1[cat]
        )
    )
)

[smpa01]

@AlexisOlson  thanks it has better performance.

[AlexisOlson · Accepted solution]

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_