Convert columns to a list

Question

Consider me as a beginner in Power BI

I have json data converted to columns by using extract and delimiter. Now the data looks like this :

cast.2          cast.3         cast.4           cast.5          id
Tom Hanks       Tim Allen      Don Rickles      Jim Varney      862
Robin Williams  Jonathan Hyde  Kirsten Dunst    Bradley Pierce  8844
Walter Matthau  Jack Lemmon    Ann-Margret      Sophia Loren    15602

I want to convert all the cast columns to a list so they're identified by the ID looking like this

Cast            ID
Tom Hanks       862
Tim Allen       862
Don Rickles     862
Jim Varney      862
Robin Williams  8844
Jonathan Hyde   8844
Kirsten Dunst   8844
Bradley Pierce  8844
Walter Matthau  15602
Jack Lemmon     15602
Ann-Margret     15602
Sophia Loren    15602

I have tried using merge columns, text.tolist, text.combine but i get different errors every time like cannot apply field access to the type text

Answer 1

Using Power Query, select the id column and select Unpivot Columns > Unpivot other columns :

Remove the attribute column, reorder the other ones and rename the Value to cast :

Or using DAX, you can create a calculated table :

Unpivoted Cast = 
UNION(
    SELECTCOLUMNS('TestTab', "Cast", 'TestTab'[cast.2], "ID", 'TestTab'[id]),
    SELECTCOLUMNS('TestTab', "Cast", 'TestTab'[cast.3], "ID", 'TestTab'[id]),
    SELECTCOLUMNS('TestTab', "Cast", 'TestTab'[cast.4], "ID", 'TestTab'[id]),
    SELECTCOLUMNS('TestTab', "Cast", 'TestTab'[cast.5], "ID", 'TestTab'[id])
)