Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello I have a column as below
Name | Accessories |
A | Bags,Pencil |
B | Bags, Bottle |
C | Bottle , Pencil |
D | Eraser, Mobile |
E | Charger,Mobile |
It should display in the below form
Name | Accessories |
A | Bags |
A | Pencil |
B | Bags |
B | Bottle |
C | Bottle |
C | Pencil |
D | Eraser |
D | Mobile |
E | Charger |
E | Mobile |
Solved! Go to Solution.
Hi @sonalisaha2310 ,
There are two ways I have tried this.
One is to make a linked table and then cross join it but the way with crossjoin could be potentially way bigger than the table you want to end up with, so heres an alternative that gives a table that's exactly the right size that you want to end up with.
Split =
VAR ToPaths =
ADDCOLUMNS (
SELECTCOLUMNS (
Table,
"@ID", Table[Name],
"@Path", SUBSTITUTE ( Table[Accessories], " ", "," )
),
"@Length", PATHLENGTH ( [@Path] )
)
VAR T =
ADDCOLUMNS (
ToPaths,
"@Cumulative", SUMX ( FILTER ( ToPaths, [@ID] <= EARLIER ( [@ID] ) ), [@Length] )
)
RETURN
ADDCOLUMNS (
SELECTCOLUMNS (
ADDCOLUMNS (
GENERATESERIES ( 1, SUMX ( T, [@Length] ) ),
"Cumulative", MINX ( FILTER ( T, [@Cumulative] >= [Value] ), [@Cumulative] )
),
"Name", MAXX ( FILTER ( T, [@Cumulative] = [Cumulative] ), [@ID] ),
"Accessories", MAXX ( FILTER ( T, [@Cumulative] = [Cumulative] ), [@Path] ),
"Accessories Number", 1 + [Cumulative] - [Value]
),
"Accessories Split", PATHITEM ( [Accessories], [Name] )
)
Just replace Table with your table name and the respective columns if you face any errors.
If your requirement is solved, please make THIS ANSWER as SOLUTION and help other users find the solution quickly. Please hit the LIKE button if this comment helps you.😊
Hi @sonalisaha2310 ,
There are two ways I have tried this.
One is to make a linked table and then cross join it but the way with crossjoin could be potentially way bigger than the table you want to end up with, so heres an alternative that gives a table that's exactly the right size that you want to end up with.
Split =
VAR ToPaths =
ADDCOLUMNS (
SELECTCOLUMNS (
Table,
"@ID", Table[Name],
"@Path", SUBSTITUTE ( Table[Accessories], " ", "," )
),
"@Length", PATHLENGTH ( [@Path] )
)
VAR T =
ADDCOLUMNS (
ToPaths,
"@Cumulative", SUMX ( FILTER ( ToPaths, [@ID] <= EARLIER ( [@ID] ) ), [@Length] )
)
RETURN
ADDCOLUMNS (
SELECTCOLUMNS (
ADDCOLUMNS (
GENERATESERIES ( 1, SUMX ( T, [@Length] ) ),
"Cumulative", MINX ( FILTER ( T, [@Cumulative] >= [Value] ), [@Cumulative] )
),
"Name", MAXX ( FILTER ( T, [@Cumulative] = [Cumulative] ), [@ID] ),
"Accessories", MAXX ( FILTER ( T, [@Cumulative] = [Cumulative] ), [@Path] ),
"Accessories Number", 1 + [Cumulative] - [Value]
),
"Accessories Split", PATHITEM ( [Accessories], [Name] )
)
Just replace Table with your table name and the respective columns if you face any errors.
If your requirement is solved, please make THIS ANSWER as SOLUTION and help other users find the solution quickly. Please hit the LIKE button if this comment helps you.😊
Hi sonalisaha2310,
This can be easily done using Power Query. Below is a screenshot showing how to accomplish it in Power Query.
I am not getting the column in Power Query Editor as the column is a calculated column , therefore I needed a dax query for it
Can you create the calculated column in Power Query and then use the delimiter to achieve the results ?
User | Count |
---|---|
77 | |
74 | |
62 | |
61 | |
45 |
User | Count |
---|---|
108 | |
102 | |
93 | |
83 | |
64 |