digest
Short Summary
Main recommendation: Use Power Query's From Folder feature with a single custom-column formula that nests functions (CSV.Document, Table.Transpose, Table.PromoteHeaders, Table.RenameColumns, Table.UnpivotOtherColumns, Table.SelectRows, Table.TransformColumnTypes) to convert multiple cross-tab CSV files into one proper, pivot-ready table. The result updates automatically when new files are added to the folder and refreshed.
Key Points
- A cross-tab layout must be converted to a column-based structure, because sorting and PivotTables require all values in a single column.
- The From Folder feature imports every CSV in a folder, so new files added later are included on refresh.
- Each file is transformed inside one custom column, with each row holding its own converted table, so the original columns can be discarded.
- Table.Combine then merges the per-file tables into one table, and the final query returned 138 rows across the files.
- The method is demonstrated with the excelisfun tutorial's Seattle airport cargo data, using four files initially and two more added to test the refresh.
Details Worth Noting
- Concrete instructions:
- Data > Get Data > From File > From Folder; select the folder; click Transform Data (not Combine or Load).
- Rename the query, then go to Add Column > Custom Column and name the column (the video uses "get transform CSVs").
- Build the formula in the formula bar, step by step:
Table.AddColumn(..., "source", each Csv.Document([Content])), then addTable.Transpose(...)to the first two rows, thenTable.PromoteHeaders(...). - Rename columns with
Table.RenameColumns(..., {{"", "Cargo Type"}, {"Column1", "Year"}})(the video uses a list within a list for old and new names; the example renames the blank column to "Cargo Type" and the second to "Year"). - Unpivot with
Table.UnpivotOtherColumns(..., {"Cargo Type", "Year"}, "Week", "Volume"). - Remove blanks with
Table.SelectRows(..., each [Volume] <> ""). - Set data types with
Table.TransformColumnTypes(Cargo Type as text, Year as Int64, and so on). - Add a final step that looks up the column with
Table[ColumnName], thenTable.Combine(...). - Use the tip of adding a step, copying its M code, then deleting the step, to recall syntax for data-type changes.
- Refresh after adding files to the folder to confirm the new records appear.
- Tip: when accepting function names from IntelliSense, type them in lowercase with no capitals or periods, or autocomplete will not work.
- A commenter noted that the cross-tab is fine for reading, but must be converted for sorting, filtering, or PivotTables.
- The video teaches a "mega formula" approach; the next video in the series uses a custom function with a parameter instead.
- The book The Transformative Magic of M Code in Power Query, Excel and Power BI is referenced for learning M code from beginner to advanced.
Bottom Line
A single nested custom-column formula, combined with the From Folder feature, turns a folder of cross-tab CSVs into one refreshable, pivot-ready table without manual copying.
