Append without loosing colums

[acanepa]

Hello,

I'm trying to put together 16 excel files. 10 excel files have 32 columns and 6 excel files have 26.

How can append the fields without loosing the 6 columns?, it would be much easier if I could use some SQL language, but my M language is awful. 

I already join the files into 2  excel files. And manually join, but after importing the data I have 3,000 row with errors and I think it's a problem how I made the join. 

So my question, Can  I do it the join inside Power Bi? without loosing columns or havign error in the rows.

Thanks in advance.

Regards,

PD: Excel is pretty similar to Power BI to import data,  the M language use a code of Table.combine. It's possible in this commando to tell, do not skip the 6 columns?, I don't care if the system put null values.

[MarcelBeug · Accepted solution]

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