Back to School - Transform class schedule to PivotTable (Power Query)


This article has moved here.

Comments (9)

  1. anonymouscommenter says:

    This post is the first in a series of posts that will walk you through one of the coolest data transformation

  2. jacopo says:

    Very cool!

  3. Ken Puls says:

    Gil, nice post. I think you should make one change though. There's no reason here to create a blank query, then enter custom M code to get at the data. Just select any cell in the initial table and go to Data --> Get & Transform --> From Table. It will
    automatically load the table, promote the headers and change the column types for you.

  4. Ken Puls says:

    Gil, just to clarify here. You still need to create the named range in order to preserve your merged cells. Providing that the name range is selected in full, however, you can simply use From Table to pull it in.

  5. Gil Raviv says:

    Thank you, Ken.
    You are right. I edited the post, with your recommendation, to "From Table" instead of "Blank Query". Note that after the definition of the name range, selecting a single cell is not be sufficient. Excel doesn't automatically recognize the entire range. But
    if we select the entire range, we can use "From Table".

  6. anonymouscommenter says:

    This is the third post in the series The Definitive Guide to Unpivot with Power Query in Excel . In this

  7. AlexExcelNY says:

    Gil, advanced code could be much easier, basic query with next step - Grouping row, here is code:
    let
    Source = Excel.CurrentWorkbook(){[Name="ClassSchedule"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Time", type time}, {"Mon", type text}, {"Tue", type text}, {"Wed", type text}, {"Thu", type text}, {"Fri", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type1",{"Mon", "Tue", "Wed", "Thu", "Fri"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filled Down", {"Time"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "none")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Day of Week"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Record.FromList( Lines.FromText([Value]), {"Class", "Teacher"})),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Class", "Teacher"}, {"Class", "Teacher"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each 30),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Custom", "Duration (Minutes)"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Value"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Day of Week", "Class", "Teacher"}, {{"Count", each List.Sum([#"Duration (Minutes)"]), type number}, {"Column", each List.Min([Time]), type time}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Day of Week", "Column", "Class", "Teacher", "Count"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"Column", "Start Time"}, {"Count", "Duration (Minutes)"}})
    in
    #"Renamed Columns2"

Skip to main content