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.

 

[SQUIDD]

Hi

Try this, put your name list in column A
These formula must be entered by pressing ctrl,shift and enter.

in B
=INDEX(Sheet1!B1:F1,SMALL(IF(Sheet1!$B$1:$F$1<>"",COLUMN(Sheet1!$B$1:$F$1)-COLUMN(Sheet1!$B$1)+1),1))

in C
=INDEX(Sheet1!B1:F1,SMALL(IF(Sheet1!$B$1:$F$1<>"",COLUMN(Sheet1!$B$1:$F$1)-COLUMN(Sheet1!$B$1)+1),2))

Then copy down

Dave

 

[alipezu]

How do I do it in Power Query? Though I am gonna try it too.

 

[SQUIDD]

Not sure about Power Query.

sorry

But the formula i posted above looks for the 1st non blank data in the range, then the 2nd non blank in the range.
In turn giving you what you asked for in your original post.

dave

 

[Matt Allington]

I have a working solution for you, but I feel that it is overly complex. I think there must be a better way. I would like to hear from MarcelBeug or Imke to hear if there is a better way

In short, I unpivoted the data, counted the number of rows per driver, merged that back to the unique list and then created a custom column to determine if it was the first school or second school. I feel there should be a way to insert a nested ID column - I just don't know how.

let
Source = Excel.CurrentWorkbook(){[Name="TableMain"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Bus Driver Name", type text}, {"Kindergarten", type text}, {"Boys School", type text}, {"Girls School Cycle1", type text}, {"Girls School Cycle2", type text}, {"Girls School Cycle3", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Bus Driver Name"}, "Attribute", "Value"),
List = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
Count = Table.Group(List, {"Bus Driver Name"}, {{"Count", each Table.RowCount(_), type number}}),
Custom1 = List,
#"Merged Queries" = Table.NestedJoin(Custom1,{"Bus Driver Name"},Count,{"Bus Driver Name"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Count"}, {"NewColumn.Count"}),
#"Added Index" = Table.AddIndexColumn(#"Expanded NewColumn", "Index", 0, 1),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
#"Merged Queries1" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {"Bus Driver Name"}, {"NewColumn.Bus Driver Name"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn1",{"Index", "Index.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NewColumn.Bus Driver Name", "Next Line"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each if [NewColumn.Count] = 1 then 1 else if [Next Line] = [Bus Driver Name] and [NewColumn.Count] = 2 then 1 else 2),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"NewColumn.Count", "Next Line"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Custom", "Bus Driver Name", "Value"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Custom", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Custom]), "Custom", "Value")
in
#"Pivoted Column"

 

[billszysz]

Sorry Matt... i am not Imke or Marcel (

let
    Source = Excel.CurrentWorkbook(){[Name="TableMain"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Bus Driver Name"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Bus Driver Name"}, {{"tbl", each Table.AddIndexColumn(_, "Idx", 1,1), type table}}),
    #"Expanded {0}" = Table.ExpandTableColumn(#"Grouped Rows", "tbl", {"Idx", "Value"}, {"Idx", "Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded {0}",{{"Idx", type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Changed Type", {{"Idx", each "School " & Text.From(_, "pl-PL"), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Idx]), "Idx", "Value")
in
    #"Pivoted Column"

Regards

 

[Matt Allington]

Thanks billszysz. soory for omitting you from the premium list of gurus

 

[Matt Allington]

So the secret sauce is the grouped rows line. I knew it would look something like that, but I didn't know how to write it correctly. Can you please provide a description of how you wrote this line and what it does.

 

[Matt Allington]

OK, I worked it out. I must have been pretty close when I tried to do it earlier. I did a group by with a count. I then tried to edit that line of code to insert the index bit. I think I forgot to change the last parameter from number to table. All good. Thanks for the learning Billszysz

 

[MarcelBeug]

A very different solution. Advantage: it runs faster (I did some tests). Disadvantage: it requires advanced editing.

let
    Source       = Excel.CurrentWorkbook(){[Name="TableMain"]}[Content],
    Typed1       = Table.TransformColumnTypes(Source,{{"Bus Driver Name", type text}, {"Kindergarten", type text}, {"Boys School", type text}, {"Girls School Cycle1", type text}, {"Girls School Cycle2", type text}, {"Girls School Cycle3", type text}}),
    AddedRecords = Table.AddColumn(Typed1, "Custom", each 
                    let
                        SchoolNames = List.RemoveMatchingItems(List.Skip(Record.FieldValues(_)),{"",null}),
                        FieldNames  = List.Transform({1..List.Count(SchoolNames)}, each "School" & Text.From(_)),
                        Record      = Record.FromList(SchoolNames,FieldNames)
                    in
                        Record),
    RemovedColumns  = Table.RemoveColumns(AddedRecords,{"Kindergarten", "Boys School", "Girls School Cycle1", "Girls School Cycle2", "Girls School Cycle3"}),
    AddedCount      = Table.AddColumn(RemovedColumns, "FieldCount", each Record.FieldCount([Custom])),
    FieldNames      = List.Transform({1..List.Max(AddedCount[FieldCount])}, each "School" & Text.From(_)),
    ExpandedRecords = Table.ExpandRecordColumn(AddedCount, "Custom", FieldNames),
    RemovedCount    = Table.RemoveColumns(ExpandedRecords,{"FieldCount"})
in
    RemovedCount