digest
Short Summary
Main recommendation: Use Power Query's "From File, From Folder" import to combine multiple text files into one table in a single step, then build a pivot chart with a slicer to compare grade statistics (GPA, standard deviation, count) across years. The video demonstrates this with three class grade text files totaling about 75,000 rows of imported data, and shows how formula-driven chart labels can update when the slicer changes the year.
Key Points
- Power Query's "From File, From Folder" option imports every text file in a folder and stacks them automatically, without importing each file separately.
- After import, the speaker removes extra columns, renames the query "Grade Data," and filters out stray repeated header-row values that can appear mid-data in large files.
- Three pivot tables are built from the same data: average (renamed GPA, formatted to one decimal), standard deviation of the sample (four decimals), and count.
- A Year slicer controls all three pivot tables, and the chart updates instantly when a different year is selected.
- Chart title and data labels are formula-linked to pivot cells, so the labels change with the slicer; the speaker notes that linking data labels to cells is an awesome feature available in Excel 2013, without specifying it was newly introduced in that version versus earlier.
Details Worth Noting
- Concrete instructions:
- Download Power Query (the speaker says to search "download Power Query"; it works in Excel 2010 and 2013).
- Go to Data > Power Query > From File > From Folder, click Browse, select the folder containing the text files, then click OK twice.
- Right-click the Binary column's file metadata and choose Remove Other Columns, then rename the query to "Grade Data."
- Click the filter arrow on the header, use "Load More" to see the full list, and uncheck any field-name values that appear mid-data.
- Click OK, then choose Close & Load To, load it to an Excel table on the existing sheet at A1.
- Insert a pivot table on a new sheet with Class in Rows and Grade in Values; change Value Field Settings to Average, name it GPA, and format to one decimal.
- Copy the table and change the calculation to Standard Deviation of the Sample (four decimals), then copy again and set to Count (zero decimals).
- Insert a Year slicer and add the Year field as a filter to support the chart title.
- Build labels in cells with formulas such as ="Student Data in "&B1 for the title, and for data labels join GPA, standard deviation, and count with CHAR(10) (a hard line return), using TEXT(...,"0.0") and TEXT(...,"0.000") to carry number formatting into the text.
- Create the default column chart (Alt, F1), then in Format Data Labels uncheck Values and select Value From Cells pointing to the label range.
- The speaker notes that some imports (Excel files, other data sources) won't be a single step and will require more work than this text-folder method.
- The chart uses formula-linked labels, so the author labels cells in green as a reminder that they are formulas.
Bottom Line
Power Query's folder import plus formula-linked pivot charts can turn many raw text files into an interactive, slicer-driven grade report without manual per-file imports.
