Unpivot and Arrange the data in Sheet1

[alipezu]

Hi All,
Please help me unpivot and arrange the data in Sheet1 using power query. My data must look same as in Sheet2..
Mr MarcelBeug helped me in the past and now I am looking forward to his help or some other expert.
My file link is here https://drive.google.com/file/d/0B9uKuwzDH_rQM2RxdVdqaG0xT1E/view

Thank You.

 

[alipezu]

Thank you MarceBeug for your help..

No, you got it wrong.

This code replaces the table type of the table in #"Removed Columns" by the table type of table Part1.
A table type is the combination of the table properties: column names, column types and any table keys.

In this case, it is used to synchronize the column names and column types of the second part with those of the first part.
It doesn't affect any table content.

 

[alipezu]

MarcelBeug
1- Did you use UI or typed in editor "Part1 = Table.TransformColumnNames(#"Removed Other Columns", each Text.Replace(_, "First ","")),"??
2- What does "_" mean here in "each Text.Replace(_, "First ",""))"??

Actuallly it is not really unpivot, rather take a set of columns, take another set of columns and glue the 2 parts together.

More detail:

I renamed the columns in the first part (remove "First ") using function Table.TransformColumnNames, which is - I guess - not a well known function as it is never used in code that is generated from the Query Editor options.

I renamed the columns of the second part by simply replacing the table type (i.e. the column names, their types and any table keys) with the table typ from part 1.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1418"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Bus Driver ID", Int64.Type}, {"Bus Driver Name", type text}, {"Mobile No. 1", Int64.Type}, {"Mobile No. 2", Int64.Type}, {"Bus No.", type text}, {"Bus Plate No.", Int64.Type}, {"Bus Type", type text}, {"Seats", Int64.Type}, {"First Trip ID", type text}, {"First School Name", type text}, {"Students Gender", type text}, {"First Bus Assistant ID", Int64.Type}, {"First Bus Assistant Name", type text}, {"First Bus Assistant Mobile", Int64.Type}, {"Second Trip ID", type text}, {"Second School Name", type text}, {"Students Gender2", type text}, {"Second Bus Assistant ID", Int64.Type}, {"Second Bus Assistant Name", type text}, {"Second Bus Assistant Mobile", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Bus Driver ID", "Bus Driver Name", "Mobile No. 1", "Mobile No. 2", "Bus No.", "Bus Plate No.", "Bus Type", "Seats", "First Trip ID", "First School Name", "Students Gender", "First Bus Assistant ID", "First Bus Assistant Name", "First Bus Assistant Mobile"}),
    Part1 = Table.TransformColumnNames(#"Removed Other Columns", each Text.Replace(_, "First ","")),

    // Notice that the next step proceeds again from step #"Changed Type:
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"First Trip ID", "First School Name", "Students Gender", "First Bus Assistant ID", "First Bus Assistant Name", "First Bus Assistant Mobile"}),
    Part2 = Value.ReplaceType(#"Removed Columns",Value.Type(Part1)),
    #"Parts Combined" = Part1 & Part2,
    #"Filtered Rows" = Table.SelectRows(#"Parts Combined", each ([Trip ID] <> null))
in
    #"Filtered Rows"

 

[alipezu]

3- Also explain "Part2 = Value.ReplaceType(#"Removed Columns",Value.Type(Part1))"
Shouldn't I do here "Part2 = Table.TransformColumnNames(#"Removed Other Columns", each Text.Replace(_, "Second ",""))"

 

[MarcelBeug]

1. Advanced Editor.
2. "_" refers to each individual current value. In general "_" is used in combination with each. In this case, the current values are the current column names.
3. I already explained in post #2 . Your alternative is also fine.