Forum Discussion

smpa01's avatar
smpa01
Community Champion
3 years ago
Solved

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 |
|-----|-----|--------|-------|
| 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

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    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_

     

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    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's avatar
    CNENFRNL
    Community Champion
    3 years ago

    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's avatar
      smpa01
      Community Champion
      3 years ago

      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's avatar
        CNENFRNL
        Community Champion
        3 years ago
        Table =
GENERATEALL(
    ALLEXCEPT( T2, T2[value] ),
    CALCULATETABLE(
        VALUES( T1[subCat] ),
        TREATAS(
            CALCULATETABLE( SUMMARIZE( T2, T2[row], T2[cat] ) ),
            T1[row],
            T1[cat]
        )
    )
)