The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 ?