Forum Discussion
Help with transforming data
Hello,
I have an Excel file that needs to be transformed and I am running into issues. I have provided an example of it below. I need to combine the first row into one column and the name will be "Courses". I then need to combine the completed, due, and system into 3 separate columns. Then, each name should therefore be duplicated for each course. So in the example, there should be four rows (2 for each name) and 6 columns (name, section, course, completed, due, and system).
I
Thank you in advance
1 import your data into Power Query Editor without headers
2 input below code as a new step after your import
=let a=Table.ToRows(PreviousStepName) in #table({"Name","Section","Course","Due","Completed","System"},List.TransformMany(List.Skip(a,2),each List.Split(List.Skip(List.Zip({a{0},_}),2),3),(x,y)=>List.FirstN(x,2)&{y{0}{0}}&List.Zip(y){1}))
1 Reply
1 import your data into Power Query Editor without headers
2 input below code as a new step after your import
=let a=Table.ToRows(PreviousStepName) in #table({"Name","Section","Course","Due","Completed","System"},List.TransformMany(List.Skip(a,2),each List.Split(List.Skip(List.Zip({a{0},_}),2),3),(x,y)=>List.FirstN(x,2)&{y{0}{0}}&List.Zip(y){1}))