Forum Discussion
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.
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
1 Reply
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