Forum Discussion
How do I move column data under other columns - unpivot doesn't work here
Hi,
I am trying to turn this dataset into the one below and unpivot doesn't quite seem to work. Any advice on this?
| Team | player1.first | player1.last | player2.first | player2.last | player3.first | player3.last |
| Green | Sally | Jensen | John | Dawson | Claire | Watson |
| Purple | Steve | Wells | Terry | Wu | Gina | Smith |
| Team | first | last |
| Green | Sally | Jensen |
| Green | John | Dawson |
| Green | Claire | Watson |
| Purple | Steve | Wells |
| Purple | Terry | Wu |
| Purple | Gina | Smith |
=#table({"Team","First","Last"},List.TransformMany(Table.ToRows(PreviousStepName),each List.Split(List.Skip(_),2),(x,y)=>{x{0}}&y))
4 Replies
=#table({"Team","First","Last"},List.TransformMany(Table.ToRows(PreviousStepName),each List.Split(List.Skip(_),2),(x,y)=>{x{0}}&y))
Thanks Daniel, that works great for the sample dataset.
However, I'm trying to apply the solution to our slightly more complex data using the following
= #table({"Ballot ID","Participant ID","Participant CustNum","VotedFlag","First Name","Last Name","Current Employer","Committee Join Date","Committee Drop Date"},List.TransformMany(Table.ToRows(#"Changed Type"),each List.Split(List.Skip(_),8),(a,b,c,d,e,f,g,h)=>{a{0}}&b))
But I don't know how to assign the 8 items using =>{a{0}}&b)...... I don't really know what this is doing.
Can you please let me know how to do this? Thanks in advance
I finally figured out that (x,y)=>{x{0}}&y is like a name,value pair so I tried the following command and it works! Thanks so much!
= #table({"Ballot ID","Participant ID","Participant CustNum","VotedFlag","First Name","Last Name","Current Employer","Committee Join Date","Committee Drop Date"},List.TransformMany(Table.ToRows(#"Removed Other Columns"),each List.Split(List.Skip(_),8),(x,y)=>{x{0}}&y))
= #table({"Ballot ID","Participant ID","Participant CustNum","VotedFlag","First Name","Last Name","Current Employer","Committee Join Date","Committee Drop Date"},List.TransformMany(Table.ToRows(#"Changed Type"),each List.Split(List.Skip(_),8),(x,y)=>List.FirstN(x,8)&y))
