csv Folder-Source with slightly different columns

[NewPBIe]

Hello everybody,

I need to dynamically adapt the list of columns from multiple csv files with slightly different columns.

I have have a folder-source, where periodically new csv are loaded. Also it is not only two tables, is a constantly growing folder.

Unfortunately I have no means to share data outside my organisation. 

fileone: 

filetwo:

desired output: 

actual output:

My output table should contain all the columns from every file and properly assign each value from every file in the same column, if the name among the different files is the same.

Thank you!

[dk_dk · Accepted solution]

Hi @NewPBIe ,

If you know all the possible column names that can occur, you can do the following:

Create a csv file in the same folder with all the other files (lets call it "csv_master") that contains all the possible column names, with 0 values.

When you connect to your folder, at the merge files step, make sure you select csv_master as the sample file:

and that should be it. The result should look something like this:

You can filter out csv_master from the Source.Name column, replace the nulls with blank or zeros as needed and you should be good to go.

If later on more columns are added to one of the csvs, you should be able to just come back to the master and add the extra columns.

Hope this works, let me know if you have any questions.

[NewPBIe]

@dk_dk Thank you. It has some manual work in it but it is still a good workaround if nothing else helps. 

[dk_dk]

Depending on the system that creates the csv files for you, you might be able to build some automation that updates the master csv file for you every time additional columns are introduced in your data ( I am thinking, Power Automate flow) but it is hard to say how without knowing more about the process that populates the folder.