How to make Output table from a Source table

[alipezu]

Hi ​MarcelBeug,
There was little confusion in my previous post. I made it clear in this post...
I want to make an 'output1' table from the 'source1' table. Where the Source1 table has IDs from 4 different tables. Also I would like more details like Driver Name, Mobile, School Name, Bus Plate numbeer, seats, Assistant name etc in the output table.
https://drive.google.com/file/d/1bTPiARpYvJ_gAWYxKuAvfaZh6lGTbtuv/view?usp=sharing

 

[citizenbh]

Output1 is not correct. You have 8 first and 4 second trips.(not 6-6)
I solved it like this. It can probably be shortened,
but I believe you will understand how it works by looking step by step
Insert the code into the advanced editor in a blank query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Trip ID", type text}, {"Trip Timing", type time, {"hh:mm"}}, {"Bus Driver ID",type text}, {"Bus No.", type text}, {"Bus Assistant ID", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "First or Second Trip", each if [Index]=0 or #"Added Index"{[Index]}[#"Bus No."]<>#"Added Index"{[Index]-1}[#"Bus No."] then "First" else "Second"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[#"First or Second Trip"]), "First or Second Trip", "Trip ID"),
    #"Added Custom1" = Table.AddColumn(#"Pivoted Column", "First Bus ***", each if [First] <> null then [Bus Assistant ID] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Second Bus ***", each if [Second] <> null then [Bus Assistant ID] else null),
    #"Sorted Rows" = Table.Sort(#"Added Custom2",{{"Bus Driver ID", Order.Ascending}, {"Trip Timing", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Bus Driver ID", "Bus No.", "First", "Second Bus ***", "Second", "First Bus ***"}),
    #"Added Custom3" = Table.AddColumn(#"Reordered Columns", "Second Trip ID", each if #"Reordered Columns"{[Index]+1}[Second]<>null then #"Reordered Columns"{[Index]+1}[Second] else ""),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Second Bus Assistant", each if #"Reordered Columns"{[Index]+1}[Second Bus ***]<>null then #"Reordered Columns"{[Index]+1}[Second Bus ***] else ""),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom4", each ([First Bus ***] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Trip Timing", "Second Bus ***", "Second"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"First", "First Trip ID"}, {"First Bus ***", "First Bus Assistant"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Index", "Bus Assistant ID"})
in
    #"Removed Columns1"

The second problem is already too late. I believe MarcelBeug will do it.

 

[citizenbh]

I see now that your first trips is 7:15 and the second at 8:00. It is necessary to create a column "First or Second Trip" based on the time of travel

 

[alipezu]

1- The trip that starts at 7:15 must go to 'First Trip' column and 8:00 must go to 'Second Trip' column.
2- Yes I have 8 first and 4 second trips and its not necessary that the trips must be equals or ballace because some drivers have a single school that starts at 8:00 and vise versa..

3- Your code gives me an error after pasting in advanced editor, saying 'Invalid Identifier'

 

[alipezu]

I have done it up to here but my bus ID and Driver Id repeating in multi rows. Please help me remove those duplicates.

let    Source = Excel.CurrentWorkbook(){[Name="Source1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Trip ID", type text}, {"Trip Timing", type time}, {"Bus Driver ID", Int64.Type}, {"Bus No.", type text}, {"Bus Assistant ID", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "First Trip ID", each if [Trip Timing] = #time(7, 15, 0) then [Trip ID] else null ),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "First Assistant ID", each if [Trip Timing] = #time(7, 15, 0) then [Bus Assistant ID] else null ),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Second Trip ID", each if [Trip Timing] = #time(8, 0, 0) then [Trip ID] else null ),
    #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Second Assistant ID", each if [Trip Timing] = #time(8, 0, 0) then [Bus Assistant ID] else null ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column3",{"Bus Driver ID", "Bus No.", "First Trip ID", "First Assistant ID", "Second Trip ID", "Second Assistant ID"})
in
    #"Removed Other Columns"

 

[citizenbh]

On link you have file for time criteria. (7:15 - first, 8:00 - second)

https://www.dropbox.com/s/9dkq8tfvldaci81/TimeCriteria_Output_from_Source_Tabele.xlsx?dl=0

 

[MarcelBeug]

I have done it up to here but my bus ID and Driver Id repeating in multi rows. Please help me remove those duplicates.

You might follow the approach I provided as a solution in your other thread. The one you dismissed so easily:

That's not what I meant..

 

[alipezu]

MarcelBeug
English is not my native language. I am so sorry some time I mean something else than I type...
In that previous post I get the following error after I past your given code. "Expression.Error: The import ResultExtra matches no exports. Did you miss a module reference?""
I can't dare to dismiss your help. You are the one who gives quick replies and more help..

 

[MarcelBeug]

I meant you could follow the same approach, not copy/paste the code.

The approach is to first select the unique bus data, split the trip data in 2 parts and join each part with the unique bus data.

In your new workbook, that would be:

let
    Source = Excel.CurrentWorkbook(){[Name="Source1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Trip ID", type text}, {"Trip Timing", type time}, {"Bus Driver ID", Int64.Type}, {"Bus No.", type text}, {"Bus Assistant ID", Int64.Type}}),
    CommonColumns = Table.SelectColumns(#"Changed Type",{"Bus Driver ID", "Bus No."}),
    UniqueBusData = Table.Distinct(CommonColumns),

    Trip1 = Table.SelectRows(#"Changed Type", each ([Trip Timing] = #time(7, 15, 0))),
    MergedUniqueBusDataAndTrip1 = Table.NestedJoin(UniqueBusData,{"Bus Driver ID"},Trip1,{"Bus Driver ID"},"Trip1",JoinKind.LeftOuter),
    ExpandedTrip1 = Table.ExpandTableColumn(MergedUniqueBusDataAndTrip1, "Trip1", {"Trip ID", "Bus Assistant ID"}, {"First Trip ID", "First Assistant ID"}),

    Trip2 = Table.SelectRows(#"Changed Type", each ([Trip Timing] = #time(8, 0, 0))),
    MergedExpandedTrip1AndTrip2 = Table.NestedJoin(ExpandedTrip1,{"Bus Driver ID"},Trip2,{"Bus Driver ID"},"Trip2",JoinKind.LeftOuter),
    ExpandedTrip2 = Table.ExpandTableColumn(MergedExpandedTrip1AndTrip2, "Trip2", {"Trip ID", "Bus Assistant ID"}, {"Second Trip ID", "Second Assistant ID"})
in
    ExpandedTrip2

 

[alipezu]

Thank you MarcelBeug ,
That's exactly how I wanted.. My table columns names are in Arabic, will apply your method and sure it will work..