Forum Discussion

arvindarvind24's avatar
arvindarvind24
Icon for Helper II rankHelper II
4 years ago
Solved

Concatenate two data from different tables without reference

Concatenate two data from different tables without reference Sample data and output Style No.(Table1) Region(Table2) Concatenate(Output) ABCD03700I BIHAR ABCD03700I,BIHAR ABCD06299 BL...
  • amitchandak's avatar
    amitchandak
    4 years ago

    arvindarvind24 , crossjoin

     

    new table = Crossjoin(Table1, Table2)

     

    or

     

    new table = addcolumns(Crossjoin(Table1, Table2) , "Concat", [Style No] & ", " & [Region]) 

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi arvindarvind24 
    Here is a sample file with the solution https://we.tl/t-IHBj2FTgwN

     

    Concatenated Table = 
SELECTCOLUMNS (
    GENERATE (
        Table1,
        ADDCOLUMNS ( 
            Table2,
            "@Concatenate", Table1[Style No.] & "," & Table2[Region]
        )
    ),
    "Concatenate", [@Concatenate]
)

     

     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

arvindarvind24 
The sample expected result does not clarify whether this is a crossjoin of the three tables or a merge between table1 and table3 then a crossjoj with table2. The complete crossjoin would be

Concatenated Table = 
SELECTCOLUMNS (
    GENERATE (
        Table1,
        GENERATE (
            Table2,
            ADDCOLUMNS ( 
                Table3,
                "@Concatenate", Table1[Style No.] & "," & Table3[Rank1] & "," & Table2[Region]
            )
        )
    ),
    "Concatenate", [@Concatenate]
)
arvindarvind24's avatar
arvindarvind24
Icon for Helper II rankHelper II
4 years ago

tamerj1 

 

If I want to use a cross join this will work?

 

oncatenatedTable =
SELECTCOLUMNS (
GENERATE (
ITEMMASTER,
GENERATE (
Measurestbl,
ADDCOLUMNS (Crossjoin(BaseStock),
"@Concatenate",
ITEMMASTER[STYLE_NUMBER] & "," & BaseStock[Region] & "," & Measurestbl[RANK11]
)
)
),
"Concatenate", [@Concatenate]
)

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi arvindarvind24 
    Please use

    Concatenated Table =
SELECTCOLUMNS (
    GENERATE (
        ITEMMASTER,
        GENERATE (
            BaseStock,
            ADDCOLUMNS (
                Measurestbl,
                "@Concatenate",
                    ITEMMASTER[Style No.] & "," & Measurestbl[Rank1] & "," & BaseStock[Region]
            )
        )
    ),
    "Concatenate", [@Concatenate]
)