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.

 

[MarcelBeug]

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]

MarcelBeug Thank you so much. that worked as I wanted.. How would I do it in opposite way? I mean how can I convert sheet2 data into sheet1 format using Power query Where my main key is Trip ID. Where each trip ID has a Driver, Bus and Assistant etc.
https://drive.google.com/file/d/0B9uKuwzDH_rQUlJSMDcxV1RyOGM/view?usp=sharing

 

[alipezu]

I think it can be done easily with excel formula but I want it to be done with Power Query..

 

[MarcelBeug]

It can only be done if you know which rows are for the first trip and which rows are for the second trip. so you need an additional column.
And if you want to do it in the opposite way, then you can't use Trip ID as key.
Below the query for the opposite way, where a Part column ("First", "Second") was added and Bus Driver ID was used as key column.

let
    Source = ResultExtra,
    CommonColumns = Table.SelectColumns(Source,{"Bus Driver ID", "Bus Driver Name", "Mobile No. 1", "Mobile No. 2", "Bus No.", "Bus Plate No.", "Bus Type", "Seats"}),
    CommonPart = Table.Distinct(CommonColumns),
    SpecificColumns = Table.RemoveColumns(Source,{"Bus Driver Name", "Mobile No. 1", "Mobile No. 2", "Bus No.", "Bus Plate No.", "Bus Type", "Seats"}),
    
    Filtered1 = Table.SelectRows(SpecificColumns, each ([Part] = "First")),
    RemovedColumns1 = Table.RemoveColumns(Filtered1,{"Part"}),
    RenameList1 = List.Transform(List.Skip(Table.ColumnNames(RemovedColumns1)), each {_, "First "&_}),
    SpecificPart1 = Table.RenameColumns(RemovedColumns1,RenameList1),
    Filtered2 = Table.SelectRows(SpecificColumns, each ([Part] = "Second")),
    RemovedColumns2 = Table.RemoveColumns(Filtered2,{"Part"}),
    RenameList2 = List.Transform(List.Skip(Table.ColumnNames(RemovedColumns2)), each {_, "Second "&_}),
    SpecificPart2 = Table.RenameColumns(RemovedColumns2,RenameList2),
    Merged1 = Table.NestedJoin(CommonPart,{"Bus Driver ID"},SpecificPart1,{"Bus Driver ID"},"SpecificPart1",JoinKind.LeftOuter),
    Merged2 = Table.NestedJoin(Merged1,{"Bus Driver ID"},SpecificPart2,{"Bus Driver ID"},"SpecificPart2",JoinKind.LeftOuter),
    
    ExpandList1 = List.Transform(RenameList1, each _{1}),
    ExpandList2 = List.Transform(RenameList2, each _{1}),
    ExpandedPart1 = Table.ExpandTableColumn(Merged2, "SpecificPart1", ExpandList1),
    ExpandedPart2 = Table.ExpandTableColumn(ExpandedPart1, "SpecificPart2", ExpandList2)
in
    ExpandedPart2

 

[alipezu]

That's not what I meant..
I think I need to open a new post where I will clear things to understand...I am so thankful to you MarcelBeug . You's a legend..

 

[MarcelBeug]

The question seemed quite obvious to me, so I really don't understand your one liner "That's not what I meant", without any further specification what would be wrong.

 

[alipezu]

marcelBeug,
I posted a new thread and its very clear I think.. I am so thankful to you for quick replies..

 

[horseyride]

How is this working? I get it is replacing the nulls in the second part with data from Part1, but why?

Part2 = Value.ReplaceType(#"Removed Columns",Value.Type(Part1)),

 

[MarcelBeug]

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.