Forum Discussion

oogundipe's avatar
oogundipe
Frequent Visitor
3 years ago
Solved

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.fir...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    =#table({"Team","First","Last"},List.TransformMany(Table.ToRows(PreviousStepName),each List.Split(List.Skip(_),2),(x,y)=>{x{0}}&y))

oogundipe's avatar
oogundipe
Frequent Visitor
3 years ago

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

oogundipe's avatar
oogundipe
Frequent Visitor
3 years ago

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))