How to turn 5 columns data into 2 columns

[alipezu]

Dear Experts,
I have school names consists of 5 columns on Sheet1.
Each driver has either one or two schools. But not more than two.
How do I shorten those 5 columns of school names to only 2 columns as shown on sheet2?
https://drive.google.com/file/d/0B9uKuwzDH_rQU1RqYy1Ta2NHZ00/view?usp=sharing

Thanks.

 

[ImkeF]

Hello together
Here come my 2 cents:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromRows({List.Select(List.Skip(Record.FieldValues(_),1), each _<>null)})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Bus Driver Name", "Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column2"}, {"Column1", "Column2"})
in
    #"Expanded Custom"

- Record.FieldValues: returns a list of all field values in the selected record (which is the current row, if you take the "_")
- List.Skip ..., 1: skips the first value of the list (the bus driver's name
- List.Select ..., each _<>null: selects only non-null-values
- Table.FromRows: creates the table with one column per list-entry

enjoy & sq

 

[MarcelBeug]

So it looks we have 2 types of solutions:
1. Unpivot and Groupby (Matt & Bill).
2. Using Lists with FieldValues (Imke & Marcel).

The latter are the fastest (Imke's seems to be faster than mine; however I added some code to account for any 3rd, 4th, 5th school).
The latter preserve the original row sort order.
Any additional schools can be handled by Bill's code and mine.

 

[ImkeF]

@ Marcel: Didn't see your answer when posting - funny that we both went the Record-way

We can transform it into a pretty robust solution, where:
1) Always the first column will be kept, irrespective of it's name (so no mention of any tables individual properties here )
2) Always as many columns are returned as there are different values available:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RcxBCsIwFATQq3yyrocYEZogpvITFxK6+GBoAyGWtt5f2waEWcws3oSgOA6pSH6RTnl9FyLVqF/OuHS2rd0w3B3ebLNvgvJSSpzJrbN8hlEOBN2dnrCtx60ybfj6sDjWDlmmUWImLEvM6e+cht3vwcaDq9hQ/wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Bus Driver Name" = _t, Kindergarten = _t, #"Boys School" = _t, #"Girls School Cycle1" = _t, #"Girls School Cycle2" = _t, #"Girls School Cycle3" = _t]),
   
    AllFields = Table.AddColumn(Source, "Custom", each Table.FromRows({List.Select(List.Skip(Record.FieldValues(_),1), each (_<>null and _<>""))})),
    RemoveOther = Table.SelectColumns(AllFields,{Table.ColumnNames(AllFields){0}, "Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(RemoveOther, "Custom", Table.ColumnNames(RemoveOther[Custom]{0}))
in
    #"Expanded Custom"

Contains some sample data, so just replace the Source-step by a reference to your table.

 

[MarcelBeug]

@Imke: I like your "table" solution over my "record" solution (as you don't need to supply column names with Table.FromRows).
Otherwise the solution is not 100% robust as the columns are based on the columns of the first row.

 

[alipezu]

I have added 'Timing' columns to each school, I think now it will be much easier to create 2 columns of those 5.
e.g. if school timing is 7:00 AM that must be in the first column and if the timing is 8:00 AM that must be in the second column.
https://drive.google.com/file/d/0B9uKuwzDH_rQVWEyTWQyMkYyYjQ/view?usp=sharing
Thank you to all of you guys for the help.

 

[MarcelBeug]

Welcome back in your own topic.

With the timing columns it is a bit easier (at least with my/Imke's solution; I think the unpivot/group by solutions will get more complicated, like in this topic), but less flexible.

Note in the code below I adjusted the automatic-data-typing in step "Typed" for timing fields from number to time, which is required for the next steps to work properly.

let
    Source = Excel.CurrentWorkbook(){[Name="TableMain"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Bus Driver Name", type text}, {"Kindergarten", type text}, {"Timing", type time}, {"Boys School", type text}, {"Timing2", type time}, {"Girls School Cycle1", type text}, {"Timing3", type time}, {"Girls School Cycle2", type text}, {"Timing4", type time}, {"Girls School Cycle3", type text}, {"Timing5", type time}}),
    AddedSchool1 = Table.AddColumn(Typed,        "School 1", each try Record.FieldValues(_){List.PositionOf(Record.FieldValues(_),#time(7,0,0))-1} otherwise null),
    AddedSchool2 = Table.AddColumn(AddedSchool1, "School 2", each try Record.FieldValues(_){List.PositionOf(Record.FieldValues(_),#time(8,0,0))-1} otherwise null),
    RemovedOtherColumns = Table.SelectColumns(AddedSchool2,{"Bus Driver Name", "School 1", "School 2"})
in
    RemovedOtherColumns

 

[billszysz]

Hi Marcel
In my previous solution i tried to do almost everything directly from UI. As you could see only one step was manually changed.
Of course we can do the job in many different ways
Now to the table with the timming columns.... I'm not sure if it is more difficult with unpivot . To be precise, here below I want to show the solution using UI only. Of course, I am pretty sure that is slower than yours but I think if we do not have the table with many thousends rows then we can use it. (becouse, as i mentioned, this is UI solution)

let
    Source = Excel.CurrentWorkbook(){[Name="TableMain"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Bus Driver Name", type text}, {"Kindergarten", type text}, {"Timing", type number}, {"Boys School", type text}, {"Timing2", type number}, {"Girls School Cycle1", type text}, {"Timing3", type number}, {"Girls School Cycle2", type text}, {"Timing4", type number}, {"Girls School Cycle3", type text}, {"Timing5", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Bus Driver Name"}, "Atrybut", "Wartość"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Indeks", 0, 1),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Wstawiono modulo", each Number.Mod([Indeks], 2), type number),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Modulo",{{"Wstawiono modulo", type text}}),
    #"Integer-Divided Column" = Table.TransformColumns(#"Changed Type1", {{"Indeks", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Integer-Divided Column",{"Atrybut"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Wstawiono modulo"]), "Wstawiono modulo", "Wartość"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Indeks"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"1", type time}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"1", Order.Ascending}}),
    #"Pivoted Column1" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{"1", type text}}, "pl-PL"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"1", type text}}, "pl-PL")[#"1"]), "1", "0")
in
    #"Pivoted Column1"

Regards

 

[billszysz]

Hi Matt
Sorry for late aswer but i am ine a different timezone .
After posting i went to sleep.
But I can see, you worked it out.

Regards

 

[MarcelBeug]

Ladies and gentlemen, watch this video, produced by MarcelBeug, based on a script written by Bill Szysz .

 

[billszysz]

Thanks, Marcel, for video. Now it looks simpler than by reading the code

Regards