Cannot combine CSVs due to different columns?

[DataStraine]

Hello all. I'm hoping someone can help me with a problem I've been trying to solve for a week now. What I'm am trying to do is combine multiple CSV files into a single table from SharePoint. The issue is that some of the CSVs are missing columns. This isn't an issue I can solve upstream sadly or I would do that. I do have this working in Power BI desktop; however, replicating it with a Data flow step-by-step has not worked and is giving me a missing column error (I do need the column).  What I'm hoping for Power Query/Data Flow to be able to do is what panda's can do which is just fill in the missing column values with nulls where the columns don't exist. 

[Anonymous · Accepted solution]

Hi @DataStraine ,

Do you want the merge to be done directly in the data flow? Can you provide me with the general structure of the table?

I have also found some similar posts, please refer to them to see if them help you.

• How can I combine different CSV files ( with different shemas ) in my dataflow creation 
• Combine files with structure change
• Combining multiple csv files from folder, each with different number and/or order of columns 

If what I have provided does not solve the problem for you，could you please provide the structure of each csv file that needs to be combine and the code to be applied when creating the dataflow.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataStraine]

I have files that do not have the same structure. Unfortunately, I cannot force them to have the same structure. If I were to combine these files with pandas, this wouldn't be a problem because it treats missing columns as null values. Is there a way to make a data flow work similarly?

[Anonymous]

Hi @DataStraine ,

In Power Query, To combine files, it's imperative that they all have the same structure and the same extension. All the files used in this example have the same structure and extension (.csv).

Please refer to the following document to see if it helps you.

My favorite clean and simple way to combine csv files in Power BI 

• Go to the Add Column tab
• Click Custom Column
• Enter the formula Csv.Document([Content])
• Click the OK button
• Go to the Transform tab
• Click “Use First Row as Headers”

• I have also found some similar posts, please refer to it to see if it helps you.

Merge CSVs in Python with different columns 

• You need to specify all the possible field names in advance to DictWriter, so you need to loop through all your CSV files twice: once to find all the headers, and once to read the data. There is no better solution, because all the headers need to be known before DictWriter can write the first line. This part would be more efficient using sets instead of lists (the in operator on a list is comparatively slow), but it won't make much difference for a few hundred headers. Sets would also lose the deterministic ordering of a list - your columns would come out in a different order each time you ran the code.
• The above code is for Python 3, where weird things happen in the CSV module without newline="". Remove this for Python 2.
• At this point, line is a dict with the field names as keys, and the column data as values. You can specify what to do with blank or unknown values in the DictReader and DictWriter constructors.

Reading data from CSV files with different columns 

• Table.Combine will do exactly 

If what I have provided does not solve the problem for you，could you please provide the structure of each csv file that needs to be combine and the code to be applied when creating the dataflow.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.