Unpivot a table of 31 columns to 12 columns

[alipezu]

Hi All,

I have a table which consists of 31 columns on Sheet1.
I want to unpivot it to 12 columns and I have done it manually on Sheet2.
Where the driver 'Tanner Straughan' along his Bus number 'B0004270' is mentioned in two rows because he has two schools with two different School monitors. And the Driver '**** Whitmore' along his bus number 'B0004550' is mentioned in only one row because he is working in only one school.

here is a link to my excel file: https://drive.google.com/file/d/0B9uKuwzDH_rQWFJQanRUaW95Vlk/view?usp=sharing

Will be very thankful if anyone helps me.

Thanks!

 

[MarcelBeug]

Steps to take:
1. Select the 5 columns with the school names and unpivot these.
2. Add a column with records for number of students, Monitor Name, Monitor ID and Monitor Mobile Number.
3. Remove the columns that are no longer needed.
4. Expand the column with records that was created in step 2.

I renamed each step of the code (except step 1). Otherwise all steps are done via standard UI options, but some typing is required when adding the custom column with records.
Maybe you want to add some finishing touches like datatype.

let
    Source = Excel.CurrentWorkbook(){[Name="TableMain"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Bus Driver Name", type text}, {"Driver ID", Int64.Type}, {"Driver Mobile1", type text}, {"Driver Mobile2", Int64.Type}, {"Bus Number", type text}, {"Number of Seats", Int64.Type}, {"Kindergarten", type text}, {"Number of students", Int64.Type}, {"Boys School", type text}, {"Number of Students2", Int64.Type}, {"Girls School Cycle1", type text}, {"Number of Students3", Int64.Type}, {"Girls School Cycle2", type text}, {"Number of Students4", Int64.Type}, {"Girls School Cycle3", type text}, {"Number of Students5", Int64.Type}, {"Bus Monitor Kindergarten", type text}, {"Monitor ID", Int64.Type}, {"Mobile Number", Int64.Type}, {"Bus Monitor Boys School", type text}, {"Monitor ID2", Int64.Type}, {"Mobile Number2", Int64.Type}, {"Bus Monitor Girls School Cycle1", type text}, {"Monitor ID3", Int64.Type}, {"Mobile Number3", Int64.Type}, {"Bus Monitor Girls School Cycle2", type text}, {"Monitor ID4", Int64.Type}, {"Mobile Number4", Int64.Type}, {"Bus Monitor Girls School Cycle3", type text}, {"Monitor ID5", Int64.Type}, {"Mobile Number5", Int64.Type}}),
    UnpivotSchools = Table.UnpivotOtherColumns(Typed, {"Bus Driver Name", "Driver ID", "Driver Mobile1", "Driver Mobile2", "Bus Number", "Number of Seats", "Number of students", "Number of Students2", "Number of Students3", "Number of Students4", "Number of Students5", "Bus Monitor Kindergarten", "Monitor ID", "Mobile Number", "Bus Monitor Boys School", "Monitor ID2", "Mobile Number2", "Bus Monitor Girls School Cycle1", "Monitor ID3", "Mobile Number3", "Bus Monitor Girls School Cycle2", "Monitor ID4", "Mobile Number4", "Bus Monitor Girls School Cycle3", "Monitor ID5", "Mobile Number5"}, "School Type", "School Name"),
    OtherData = Table.AddColumn(UnpivotSchools, "Custom", each if [School Type]= "Kindergarten" then [Number of Students = [Number of students], Monitor Name = [Bus Monitor Kindergarten], Monitor ID = [Monitor ID], Monitor Mobile Number = [Mobile Number]] else 
if [School Type] = "Boys School" then [Number of Students = [Number of Students2], Monitor Name = [Bus Monitor Boys School], Monitor ID = [Monitor ID2], Monitor Mobile Number = [Mobile Number2]] else
if [School Type] = "Girls School Cycle1" then [Number of Students = [Number of Students3], Monitor Name = [Bus Monitor Girls School Cycle1], Monitor ID = [Monitor ID3], Monitor Mobile Number = [Mobile Number3]] else
if [School Type] = "Girls School Cycle2" then [Number of Students = [Number of Students4], Monitor Name = [Bus Monitor Girls School Cycle2], Monitor ID = [Monitor ID4], Monitor Mobile Number = [Mobile Number4]] else
[Number of Students = [Number of Students5], Monitor Name = [Bus Monitor Girls School Cycle3], Monitor ID = [Monitor ID5], Monitor Mobile Number = [Mobile Number5]]),
    ColumnsRemoved = Table.SelectColumns(OtherData,{"Bus Driver Name", "Driver ID", "Driver Mobile1", "Driver Mobile2", "Bus Number", "Number of Seats", "School Type", "School Name", "Custom"}),
    CustomExpanded = Table.ExpandRecordColumn(ColumnsRemoved, "Custom", {"Number of Students", "Monitor Name", "Monitor ID", "Monitor Mobile Number"}, {"Number of Students", "Monitor Name", "Monitor ID", "Monitor Mobile Number"})
in
    CustomExpanded

 

[MarcelBeug]

... Otherwise all steps are done via standard UI options ...

Forgot to mention that I renamed the unpivoted columns to "School Type" and "School Name":

...
    UnpivotSchools = Table.UnpivotOtherColumns(Typed, {"Bus Driver Name", "Driver ID", "Driver Mobile1", "Driver Mobile2", "Bus Number", "Number of Seats", "Number of students", "Number of Students2", "Number of Students3", "Number of Students4", "Number of Students5", "Bus Monitor Kindergarten", "Monitor ID", "Mobile Number", "Bus Monitor Boys School", "Monitor ID2", "Mobile Number2", "Bus Monitor Girls School Cycle1", "Monitor ID3", "Mobile Number3", "Bus Monitor Girls School Cycle2", "Monitor ID4", "Mobile Number4", "Bus Monitor Girls School Cycle3", "Monitor ID5", "Mobile Number5"}, "[B]School Type", "School Name")[/B],
...

 

[alipezu]

Thank you so much MarcelBeug,
What book or any other source to learn Power Query from??

 

[MarcelBeug]

Personally I only acquired the book "M is for (Data) Monkey" by Ken Puls and Miguel Escobar, which I regard as a good startup to learn Power Query.

Other books are available, but to my knowledge there is no book covering all Power Query (M) Language in full depth.
I did my own research, going through Microsoft's Power Query Formula Language Reference for a couple of months (testing and documenting) and still researching (currently documenting all Power Query menu options in Power BI Desktop).
Otherwise you can find useful blogs and forums on the internet.

Last but not least I get some practice from questions like yours, so well appreciated.

 

[alipezu]

MarcelBeug, Can you explain step by step. Did you add the Custom column by inserting a formula in 'OtherData = Table.AddColumn' step or By clicking on the 'conditional Column'?
I have a similar thread here http://www.mrexcel.com/forum/power-...nto-7-required-columns-using-power-query.html
and the experts unpivoted columns by adding comparatively more steps than yours. If they could do the way you did it in a single step then why they did it using too many steps.?
I would love if you explain it clearly so it will help me unpivoting my columns in future.
Thank you.

 

[MarcelBeug]

The steps are explained in step 2, but you're right: I didn't mention which option I chose for step 2: it was "Add custom column" and I typed the formula with some copying and pasting.

You can't expect from me an analysis of your other thread and why other helpers would come to another solotion.
Maybe the others didn't think of combining data in records (which is not quite a standard solution as far as I know), or because the information you provide yourself is very, very limited.
For this thread it took me quite some time to discover the logic behind your question: you just give 2 sheets with 31 and 12 columns only stating that you need a row per school, but not explaining how the other columns should be calculated, If I would have started a topic with such a question, I would have far more extensively explained the logic behind my request.

 

[alipezu]

Thank You MarcelBeug for your help.