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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors