topic Re: Concatenate two data from different tables without reference in DAX Commands and Tips

Concatenate two data from different tables without reference

arvindarvind24 — Tue, 28 Jun 2022 04:57:41 GMT

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

Re: Concatenate two data from different tables without reference

amitchandak — Tue, 28 Jun 2022 05:24:54 GMT

arvindarvind24 , crossjoin

new table = Crossjoin(Table1, Table2)

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

Re: Concatenate two data from different tables without reference

Arul — Tue, 28 Jun 2022 05:32:18 GMT

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

Re: Concatenate two data from different tables without reference

tamerj1 — Tue, 28 Jun 2022 05:59:41 GMT

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

Re: Concatenate two data from different tables without reference

arvindarvind24 — Tue, 28 Jun 2022 06:56:02 GMT

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.

Re: Concatenate two data from different tables without reference

tamerj1 — Tue, 28 Jun 2022 07:23:17 GMT

arvindarvind24
Sure Please provide more details

Re: Concatenate two data from different tables without reference

arvindarvind24 — Tue, 28 Jun 2022 07:37:44 GMT

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

Re: Concatenate two data from different tables without reference

tamerj1 — Tue, 28 Jun 2022 08:01:01 GMT

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

Re: Concatenate two data from different tables without reference

arvindarvind24 — Tue, 28 Jun 2022 10:02:38 GMT

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

Re: Concatenate two data from different tables without reference

tamerj1 — Tue, 28 Jun 2022 10:34:24 GMT

Hi arvindarvind24
Please use

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