Load multiple csv files and combine them

[Anonymous]

I need to load 300+ csv files into a PowerBI, then combine some of them using “Append” function. They may have different number of columns, so I can’t use “Combine Files”. It shifts the data to the column with incorrect heading.

I created a query to get the list of files I need to load, then load them one at a time.

let

    Source = SharePoint.Files("https://cummins365.sharepoint.com/sites/GRP_CC44958", [ApiVersion = 15]),

    #"Filtered Rows2" = Table.SelectRows(Source, each ([Extension] = ".csv")),

    #"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each Text.StartsWith([Name], "PG CX Consulting")),

    #"Sorted Rows" = Table.Sort(#"Filtered Rows3",{{"Name", Order.Descending}})

in

    #"Sorted Rows"

I was able to load about 10 files without any issue. But after I connect more than 10 tables in the query mode, then tried to load these tables, I got an error message as follows:

Failed to save modifications to the server. Error returned: 'The total number of columns in the model is 17196, which exceeds the limit of 16000. '.

I can work around this by loading a couple of files a time, but this is very time consuming and not efficient.

I am looking for answers to the following questions:

1. Is there a more efficient way to merge multiple tables with different number of columns without shifting data from one column to another?
2. Is there a limitation of Power BI on number of tables it can contain?
3. Why did I get the error message for exceeding the number of columns allow? None of these csv files exceed the maximum number of columns that allow. The maximum number of columns the files have is about 250.

[Anonymous]

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.

[Anonymous]

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.

[CoreyP]

Yikes. That sounds like quite the mess. Obviously, to combine files, they need to have consistent columns, in both name and order. Depending on how much these files vary, you could be able to create a query function that transforms the files into the desired format before combining. Best of luck!

[Anonymous]

Thank you. 

I am trying to find a more flexible solution, in case there are more columns been added later on. The data source came from a survey result and they may add more questions in the future (which will result in more columns).

The Append function in Power BI works well as it will dectcte the column headers and merge the columsn with the same header, and add "null" value to those columns that may not exist in a file. 

[vicky_]

From the description you've given, it sounds like the issue is that your source data isn't formatted the same way. Of course, the easiest way would be the fix the data at the source - whether that be by padding any missing columns or removing columns that aren't going to be used. Since you are on sharepoint, which acts like a folder, in my experience, the Combine Files function is the best way to load in the data.

For your second question - technically no, there's no limits to the number of tables. But here's an interesting discussion on the limits of powerBI: https://community.fabric.microsoft.com/t5/Desktop/Power-BI-limit-for-number-of-tables/m-p/386845#:~:....

And lastly, i think the exceeding the column limit is because you have 250 (column) * 10 tables, which is 25k columns in total (much higher than the 16k allowed for ALL tables you've loaded in)

[Anonymous]

Hi Vicky,

The files came from another source and I don't want to padding the files to make it consistent. I think the Append function will work well as long as I can keep on adding the file to the Append query. I will pick the one with most columns as the first file, then append the rest of them to this file.

250*10 = 2.5K, not 25K. And since I am using the Append function, I am not adding more columns. Just "Stacking" the dataset one after another. This will add more rows, not columns. Unless the database structure in Power BI is not using the "Stacking", but adding more columns one table aftger another. 

Thank you.