Concatenate two data from different tables without reference

[arvindarvind24]

Concatenate two data from different tables without reference

Sample data and output

Style No.(Table1)	Region(Table2)	Concatenate(Output)
ABCD03700I	BIHAR	ABCD03700I,BIHAR
ABCD06299	BLR, MYS & TMKR	ABCD03700I,BLR, MYS & TMKR
ABCD08461	UTTAR PRADESH	ABCD03700I,UTTAR PRADESH
ABCD08684B	TAMIL NADU	ABCD03700I,TAMIL NADU
ABCD08684E	TELANGANA	ABCD03700I,TELANGANA
ABCD09297	ORISSA	ABCD03700I,ORISSA
ABCD09302	MH,GOA & GJ	ABCD03700I,MH,GOA & GJ
ABCD09351	DL & PB	ABCD03700I,DL & PB
ABCD09778		ABCD06299,BIHAR
ABCD09838B		ABCD06299,BLR, MYS & TMKR
		ABCD06299,UTTAR PRADESH
		ABCD06299,TAMIL NADU
		ABCD06299,TELANGANA
		ABCD06299,ORISSA
		ABCD06299,MH,GOA & GJ
		ABCD06299,DL & PB
		ABCD08461,BIHAR
		ABCD08461,BLR, MYS & TMKR
		ABCD08461,UTTAR PRADESH
		ABCD08461,TAMIL NADU
		ABCD08461,TELANGANA
		ABCD08461,ORISSA
		ABCD08461,MH,GOA & GJ
		ABCD08461,DL & PB
		ABCD08684B,BIHAR
		ABCD08684B,BLR, MYS & TMKR
		ABCD08684B,UTTAR PRADESH
		ABCD08684B,TAMIL NADU
		ABCD08684B,TELANGANA
		ABCD08684B,ORISSA
		ABCD08684B,MH,GOA & GJ
		ABCD08684B,DL & PB
		ABCD08684E,BIHAR
		ABCD08684E,BLR, MYS & TMKR
		ABCD08684E,UTTAR PRADESH
		ABCD08684E,TAMIL NADU
		ABCD08684E,TELANGANA
		ABCD08684E,ORISSA
		ABCD08684E,MH,GOA & GJ
		ABCD08684E,DL & PB

@Greg_Deckler @Ashish_Mathur 

[amitchandak · Accepted solution]

@arvindarvind24 , crossjoin

new table = Crossjoin(Table1, Table2)

or

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

[tamerj1 · Accepted solution]

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

[arvindarvind24]

Hi @tamerj1 ,

Thank you for ur Solution it's working fine and if we need to add a new data from table 3 to

Concatenate.

[tamerj1]

@arvindarvind24 
Sure Please provide more details

[arvindarvind24]

Please find the details below.

Style No.(Table1)	Rank1(Table3-Measurestbl)	Region(Table2)	Concatenate(Output)
ABCD03700I	1	BIHAR	ABCD03700I,1,BIHAR
ABCD06299	0	BLR, MYS & TMKR	ABCD03700I,1,BLR, MYS & TMKR
ABCD08461	2	UTTAR PRADESH	ABCD03700I,1,UTTAR PRADESH
ABCD08684B	4	TAMIL NADU	ABCD03700I,1,TAMIL NADU
ABCD08684E	0	TELANGANA	ABCD03700I,1,TELANGANA
ABCD09297	15	ORISSA	ABCD03700I,1,ORISSA
ABCD09302	10	MH,GOA & GJ	ABCD03700I,1,MH,GOA & GJ
ABCD09351	1	DL & PB	ABCD03700I,1,DL & PB
ABCD09778	1		ABCD06299,0,BIHAR
ABCD09838B	0		ABCD06299,0,BLR, MYS & TMKR
			ABCD06299,0,UTTAR PRADESH
			ABCD06299,0,TAMIL NADU
			ABCD06299,0,TELANGANA
			ABCD06299,0,ORISSA
			ABCD06299,0,MH,GOA & GJ
			ABCD06299,0,DL & PB
			ABCD08461,2,BIHAR
			ABCD08461,2,BLR, MYS & TMKR
			ABCD08461,2,UTTAR PRADESH
			ABCD08461,2,TAMIL NADU
			ABCD08461,2,TELANGANA
			ABCD08461,2,ORISSA
			ABCD08461,2,MH,GOA & GJ
			ABCD08461,2,DL & PB
			ABCD08684B,4,BIHAR
			ABCD08684B,4,BLR, MYS & TMKR
			ABCD08684B,4,UTTAR PRADESH
			ABCD08684B,4,TAMIL NADU
			ABCD08684B,4,TELANGANA
			ABCD08684B,4,ORISSA
			ABCD08684B,4,MH,GOA & GJ
			ABCD08684B,4,DL & PB
			ABCD08684E,0,BIHAR
			ABCD08684E,0,BLR, MYS & TMKR
			ABCD08684E,0,UTTAR PRADESH
			ABCD08684E,0,TAMIL NADU
			ABCD08684E,0,TELANGANA
			ABCD08684E,0,ORISSA
			ABCD08684E,0,MH,GOA & GJ
			ABCD08684E,0,DL & PB
			ABCD09297,15,BIHAR
			ABCD09297,15,BLR, MYS & TMKR
			ABCD09297,15,UTTAR PRADESH
			ABCD09297,15,TAMIL NADU
			ABCD09297,15,TELANGANA
			ABCD09297,15,ORISSA
			ABCD09297,15,MH,GOA & GJ
			ABCD09297,15,DL & PB

[tamerj1]

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

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

Hi @arvindarvind24 
Please use

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

[Arul]

@arvindarvind24 ,

you can also try to merge the tables in Power Query Editor as mentioned in the below community thread.

https://community.powerbi.com/t5/Desktop/Concatenate-String-Fields-From-Different-Tables/m-p/1425618 

Thanks,

Arul

[amitchandak · Accepted solution]

@arvindarvind24 , crossjoin

new table = Crossjoin(Table1, Table2)

or

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