cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
acanepa
Resolver I
Resolver I

Append without loosing colums

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.

 

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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

 

Specializing in Power Query Formula Language (M)

View solution in original post

1 REPLY 1
MarcelBeug
Community Champion
Community Champion

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

 

Specializing in Power Query Formula Language (M)

View solution in original post

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.