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

  • [アーティクル]

This article has moved here.

Comments

  • Anonymous
    September 14, 2015
    This post is the first in a series of posts that will walk you through one of the coolest data transformation
  • Anonymous
    September 14, 2015
    nice
  • Anonymous
    September 15, 2015
    Very cool!
  • Anonymous
    September 15, 2015
    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.
  • Anonymous
    September 15, 2015
    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.
  • Anonymous
    September 15, 2015
    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".
  • Anonymous
    September 27, 2015
    This is the third post in the series The Definitive Guide to Unpivot with Power Query in Excel . In this
  • Anonymous
    September 27, 2015
    Next post in The Definitive Guide to Unpivot with Power Query in Excel is ready:http://blogs.technet.com/b/gilraviv/archive/2015/09/27/transform-nested-table-to-pivottable.aspx
  • Anonymous
    January 15, 2016
    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"