Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
oogundipe
Frequent Visitor

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?

 

Teamplayer1.firstplayer1.lastplayer2.firstplayer2.lastplayer3.firstplayer3.last
GreenSallyJensenJohnDawsonClaireWatson
PurpleSteveWellsTerryWuGinaSmith

 

 

Teamfirstlast
GreenSally Jensen
GreenJohnDawson
GreenClaireWatson
PurpleSteveWells
PurpleTerryWu
PurpleGinaSmith
1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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

View solution in original post

4 REPLIES 4
oogundipe
Frequent Visitor

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

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

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

wdx223_Daniel
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors