The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Solved! Go to Solution.
@arvindarvind24 , crossjoin
new table = Crossjoin(Table1, Table2)
or
new table = addcolumns(Crossjoin(Table1, Table2) , "Concat", [Style No] & ", " & [Region])
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]
)
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]
)
Hi @tamerj1 ,
Thank you for ur Solution it's working fine and if we need to add a new data from table 3 to
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 |
@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]
)
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]
)
Hi @arvindarvind24
Please use
Concatenated Table =
SELECTCOLUMNS (
GENERATE (
ITEMMASTER,
GENERATE (
BaseStock,
ADDCOLUMNS (
Measurestbl,
"@Concatenate",
ITEMMASTER[Style No.] & "," & Measurestbl[Rank1] & "," & BaseStock[Region]
)
)
),
"Concatenate", [@Concatenate]
)
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
@arvindarvind24 , crossjoin
new table = Crossjoin(Table1, Table2)
or
new table = addcolumns(Crossjoin(Table1, Table2) , "Concat", [Style No] & ", " & [Region])
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
33 | |
20 | |
16 | |
15 |
User | Count |
---|---|
56 | |
50 | |
36 | |
35 | |
31 |