Append without loosing colums

It can be done if you have your Excel file contents in a column with tables.

Below an example of importing Excel files from a folder.

I included comments to clarify and indicate if steps are done via the UI and which parts are done manually.

After each step (ecept the first) I renamed the step to a string without spaces for better readibility of the code.

The trick is in the last 3 lines where the table column is expanded using unique column names from all input tables.

let
    // UI - Get files from folder
    Source = Folder.Files("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Combine tables without losing columns"),

    // UI - Filter only files with filename starting with "Input"
    OnlyInputFiles = Table.SelectRows(Source, each Text.StartsWith([Name], "Input")),

    // UI - Select Content column and removve all other columns
    KeepContentColumn = Table.SelectColumns(OnlyInputFiles,{"Content"}),

    // Partly UI - Add custom column and manually add code Excel.Workbook([Content])
    AddedExcelWorkbookContent = Table.AddColumn(KeepContentColumn, "Custom", each Excel.Workbook([Content])),

    // Ui - Remove Content column
    RemovedContentColumn = Table.RemoveColumns(AddedExcelWorkbookContent,{"Content"}),

    // Ui - Expand "Custom" column with tables. These are tables with data about the Excel files (like metadata)
    ExpandedWorkBookContent = Table.ExpandTableColumn(RemovedContentColumn, "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),

    // UI - Select Rows with Kind = "Table" (in this example the data in input files is organized in tables)
    OnlyRowsWithTables = Table.SelectRows(ExpandedWorkBookContent, each ([Kind] = "Table")),

    // UI - Select Data column and remove other columns
    KeepTablesOnly = Table.SelectColumns(OnlyRowsWithTables,{"Data"}),

    // Partly UI - Add custom column and manually add code Table.ColumnNames([Data])
    // This give you a column with lists containg the column names of each table
    AddedColumnNames = Table.AddColumn(KeepTablesOnly, "FieldNames", each Table.ColumnNames([Data])),

    // Partly UI: expand the Data column with tables, accept the defaults and replace the code after "Data" with the code below
    ExpandedTablesUsingColumnNames = Table.ExpandTableColumn(AddedColumnNames, "Data", List.Distinct(List.Combine(AddedColumnNames[FieldNames]))),

    // UI - Remove the column with FieldNNames
    RemovedColumnNames = Table.RemoveColumns(ExpandedTablesUsingColumnNames,{"FieldNames"})
in
    RemovedColumnNames