Forum Discussion

MandyL's avatar
MandyL
Icon for Helper I rankHelper I
3 years ago
Solved

Can we split the text column into row?

Hi Team,   In the Power Query Editor page, it’s easy to use the “Split Column by Delimiter” button to transfer the Table A’s data to Table B. In the dataset view, it is not easy to do as we don’t h...
@Dax help
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi MandyL 
    Please refer to attached sample file with the solution

    TableB = 
GENERATE ( 
    VALUES ( TableA[ID] ),
    VAR String = CALCULATE ( SELECTEDVALUE ( TableA[Member] ) )
    VAR Items = SUBSTITUTE ( String, ", ", "|" )
    VAR Length = PATHLENGTH ( Items )
    VAR T = GENERATESERIES ( 1, Length, 1 )
    RETURN
        SELECTCOLUMNS ( T, "Member", PATHITEM ( Items, [Value] ) )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi MandyL 
Please refer to attached sample file with the solution

TableB = 
GENERATE ( 
    VALUES ( TableA[ID] ),
    VAR String = CALCULATE ( SELECTEDVALUE ( TableA[Member] ) )
    VAR Items = SUBSTITUTE ( String, ", ", "|" )
    VAR Length = PATHLENGTH ( Items )
    VAR T = GENERATESERIES ( 1, Length, 1 )
    RETURN
        SELECTCOLUMNS ( T, "Member", PATHITEM ( Items, [Value] ) )
)
MandyL's avatar
MandyL
Icon for Helper I rankHelper I
3 years ago

Thank you so much for your help. 😊