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])
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
17 | |
16 | |
15 | |
14 | |
11 |
User | Count |
---|---|
36 | |
26 | |
20 | |
19 | |
17 |