Skip to content
TheChatArchiveThe Chat Archive
o4-mini-2025-04-16· OpenAIHugging Face LMArena Human Preference 140K

Please explain how to combine files from the same folder in M-Code for Power Query.…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with chatgpt-4o-latest-20250326’s answer.
  1. User

    Please explain how to combine files from the same folder in M-Code for Power Query. Thank you.

  2. 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

    1. Get folder contents
      Source = Folder.Files("C:\\Path\\To\\Your\\Folder")
      
    2. Filter down to the files you actually want (e.g. all .csv files or all .xlsx files)
    3. Read each file into a table with Table.AddColumn + File.Contents + the appropriate parser (Csv.Document, Excel.Workbook, etc.)
    4. Select/expand the inner tables
    5. 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! 😊

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.