o4-mini-2025-04-16· OpenAI
LMArena Human Preference 140K
Please explain how to combine files from the same folder in M-Code for Power Query.…
- User
Please explain how to combine files from the same folder in M-Code for Power Query. Thank you.
- Assistant
Here’s a step-by-step pattern (and two full M-code examples) showing how to pull all files from one folder and stitch them together in Power Query’s M language.
1) The overall pattern
- Get folder contents
Source = Folder.Files("C:\\Path\\To\\Your\\Folder") - Filter down to the files you actually want (e.g. all
.csvfiles or all.xlsxfiles) - Read each file into a table with
Table.AddColumn+File.Contents+ the appropriate parser (Csv.Document,Excel.Workbook, etc.) - Select/expand the inner tables
- Combine them with
Table.Combine
2) Example 1: Combine all CSVs in a folder
let // 1) List all files in the folder Source = Folder.Files("C:\\MyFolder\\Data"), // 2) Keep only .csv files CsvFiles = Table.SelectRows(Source, each Text.EndsWith([Name], ".csv")), // 3) Parse each CSV into a table ParsedCSVs = Table.AddColumn( CsvFiles, "Data", each Csv.Document( File.Contents([Folder Path] & [Name]), [Delimiter = ",", Encoding = 1252, QuoteStyle = QuoteStyle.None] ) ), // 4) Clean up columns: just keep Name + parsed table KeepCols = Table.SelectColumns(ParsedCSVs, {"Name", "Data"}), // 5) Expand all inner columns (auto-detects column names) Expanded = Table.ExpandTableColumn(KeepCols, "Data", Table.ColumnNames(KeepCols[Data]{0})), // 6) (Optional) Promote first row to headers if needed PromotedHeaders = Table.PromoteHeaders(Expanded, [PromoteAllScalars = true]) in PromotedHeaders
3) Example 2: Combine all Excel workbooks in a folder
let // 1) List all files in the folder Source = Folder.Files("C:\\MyFolder\\Reports"), // 2) Filter to .xlsx files ExcelFiles = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx")), // 3) Convert each binary to its first worksheet’s table Imported = Table.AddColumn( ExcelFiles, "Data", each let wb = Excel.Workbook(File.Contents([Folder Path] & [Name]), true), sheet = Table.SelectRows(wb, each [Kind] = "Sheet"){0}[Data] // first sheet in sheet ), // 4) Keep only the Data column and expand KeepAndExpand = Table.ExpandTableColumn( Table.SelectColumns(Imported, {"Name", "Data"}), "Data", Table.ColumnNames(Imported[Data]{0}) ), // 5) Promote headers if needed Final = Table.PromoteHeaders(KeepAndExpand, [PromoteAllScalars = true]) in Final
4) A more compact “List.Transform” approach
If you don’t need the file names and only want to concatenate all tables, you can do:
let Source = Folder.Files("C:\\MyFolder\\Data"), CsvFiles = Table.SelectRows(Source, each Text.EndsWith([Name], ".csv")), // Turn the [Content] column (binaries) into a list of tables Tables = List.Transform(CsvFiles[Content], each Csv.Document(_, [Delimiter=",", Encoding=1252])), // Combine all tables into one Combined = Table.Combine(Tables), Result = Table.PromoteHeaders(Combined, [PromoteAllScalars=true]) in Result
With those patterns you can adjust:
• parsers (Csv.Document,Json.Document,Excel.Workbook, etc.)
• filters on file names or extensions
• which sheets/tables to pick
• header promotion, data types, etc.Hope this helps you stitch all your folder files into one clean table! 😊
- Get folder contents
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.