Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |