Unpivot and Arrange the data in Sheet1

MarcelBeug

Well-known Member
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.
Code:
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"
 
Last edited:

MarcelBeug

Well-known Member
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.
Code:
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
 
Last edited:

alipezu

New Member
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

Well-known Member
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

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

horseyride

Board Regular
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

Well-known Member
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.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top