Prepare an invigilation schedule for each teacher by different time periods

{ 5 Comments }

By Ashish Mathur · · , , · Leave a comment

Imagine a multi column exam invigilation schedule with the following information

  1. S. No.
  2. Name of staff
  3. Designation of staff member
  4. Two columns for each day on which there is an exam – one for Morning and another for Afternoon
  5. A * under each column if that particular staff member has to be an invigilator during that time period
S. No. Name of staff Designation 09/11/2017-Morning 09/11/2017-Afternoon 10/11/2017-Morning 10/11/2017-Afternoon
1 Tom Lecturer * * *
2 Sam Lecturer * *

This dataset stretches into many more columns.  So if the exams last for 15 days, there will be 30 columns.  The objective is to condense the column expanding dataset into a 5 column one – S. No., Name of Staff, Designation, Morning and Evening.  Under the Morning and Evening columns, the different dates have to be separated by commas in that one single cell.  The expected result is:

Name of staff S. No. Designation Morning Afternoon
Tom 1 Lecturer 09/11/2017, 10/11/2017, 11/11/2017, 13/11/2017, 14/11/2017, 16/11/2017, 17/11/2017 10/11/2017, 13/11/2017, 14/11/2017, 15/11/2017, 16/11/2017, 17/11/2017
Sam 2 Lecturer 09/11/2017, 13/11/2017, 15/11/2017, 16/11/2017 09/11/2017

I can think of 3 ways to solve this problem. Here’s a brief about each of those methods:

  1. Using Power Query and Excel’s functions – Using Power Query, one can first unpivot the data and then use the TEXTJOIN() function.  This would be an array formula.
  2. Using Power Query only – Using Power Query, one can first unpivot the data and then modify the code to concatenate all dates in a single cell for that invigilator.  One can then Pivot the data in Power Query itself
  3. Using Power Query and PowerPivot – Using Power Query, one can first unpivot the data and then use the CONCATENATEX() function of the PowerPivot.

As you can observe, the common thread in all solutions is that one has to first unpivot the dataset.

You may refer to all my 3 solutions in this workbook.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • Below is an alternative solution adapted from a dynamic unpivot technique I developed. It has the added benefit of automatically adjusting to new dimension columns as they are added to the dataset:

    let
    Source = Excel.CurrentWorkbook(){[Name=”Table3″]}[Content],
    // A better way might be to change Text.Start to Text.BeforeDelimiter(_,”-“)
    ColumnsToUnpivot = List.Select(Table.ColumnNames(Source), each not Value.Is(Value.FromText(Text.Start(_, 10)), type date)),
    #”Unpivoted Columns” = Table.UnpivotOtherColumns(Source, ColumnsToUnpivot, “Attribute”, “Value”),
    #”Split Column by Delimiter” = Table.SplitColumn(#”Unpivoted Columns”, “Attribute”, Splitter.SplitTextByEachDelimiter({“-“}, QuoteStyle.Csv, false), {“Date”, “TimeOfDay”})
    in
    #”Split Column by Delimiter”

    Reply

    • Sorry, I forgot to say that my solution is dependent on your locale, i.e. someone from America (MM/DD) will see different results than someone from Australia (MM/DD).

      Reply

  • I had great fun with this challenge. Sharing my code with you for a different approach:

    let
    Source = Excel.CurrentWorkbook(){[Name=”Table3″]}[Content],
    #”Unpivoted Other Columns” = Table.UnpivotOtherColumns(Source, {“S. No.”, “Name of staff”, “Designation”}, “Attribute”, “Value”),
    #”Split Column by Delimiter” = Table.SplitColumn(#”Unpivoted Other Columns”, “Attribute”, Splitter.SplitTextByDelimiter(“-“, QuoteStyle.Csv), {“Date”, “DayPeriod”}),
    #”Removed Columns” = Table.RemoveColumns(#”Split Column by Delimiter”,{“Value”}),
    #”Pivoted Column” = Table.Pivot(#”Removed Columns”, List.Distinct(#”Removed Columns”[DayPeriod]), “DayPeriod”, “Date”, each Text.Combine(_, “, “)),
    #”Change Type to Text for All” = Table.TransformColumnTypes( #”Pivoted Column”,
    List.Transform(
    Table.ColumnNames(#”Pivoted Column”),
    each { _, type text}))
    in
    #”Change Type to Text for All”

    Reply