Forum Discussion
Load multiple csv files and combine them
Hi Anonymous ,
Please try to use Table.Combine.
Merging Multiple Tables with Different Column Counts:
To merge multiple tables with different numbers of columns without shifting data, you can use the `Table.Combine` function in Power Query. This function is more flexible than the "Append" feature in the Power BI interface and allows you to combine tables even if they have different columns.
Here's a general approach:
- Load the CSV files into Power Query.
- Use Table.Combine to append the tables together.
- Handle any missing columns by adding custom columns with null values where necessary to align the data correctly.
Table.Combine - PowerQuery M | Microsoft Learn
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Rongtie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
With due respect, I don’t think Table.Combined is necessary more flexible than the “Append” function. It depends on how you merge tables.
Based on your descriptions of using Table Combined, once I combine the tables, the data in various columns already been shifted. Therefore, I can’t add customized columns to individual tables. In addition, I am hoping that I don’t have to manipulate the original data files, but do all the transformation in Powre BI.
What I am trying to achieve is to load multiple csv files into Power BI, then find an efficient way to merge them with matching columns. These csv files are survey results from our customers. Each column representing either survey questions/answers or attributes to customers and transactions. The csv files evolved since the beginning of the year, hence they have different number of columns.
My experience is that both “Table Combined” and “Append” has their pros and cons. A combination of these two functions will be the optimized solution.
For Table Combined, it does not require user to load all individual csv files into Power BI as tables. We can create a view for the list of files that need to be combined, then use the “Table Combine” function to combine them all. But this function only allows tables with exact the same number of columns and exact the same sequence of columns. If the number of columns or the sequence of the columns are different, then the data in each columns got messed up.
For Append function, it allows users to combine multiple tables even the number of columns or the sequence of the columns are different. As they will find the matching column header and combine them. The downside of this function is that it will require users to load all csv files into table. Then combine them. This is a fairly manual process, and we need to ensure the first file has the most columns. This will ensure when we append a file with less columns, the missed columns will be assigned with null values automatically.
The ideal solution will be using Table Combined function to combine a list of tables (without load all files into a table), then allow the combine function to automatically find the matching columns to merge, and assign null values to missing columns.
Thank you.