Load data from folder multiple CSV files different schema (Power Query) - Possible?

[rodneyc8063_1]

I was recently given a data dump of roughly 50 CSV files - Each CSV file represents a table of data, each being a different table/column/schema structure.

I know I can "combine and load" if all the CSV files were the same and located in one folder

I was wondering in this case, if each CSV is a different schema - How can I load all of these?

Do I actually have to go to each CSV file individually and load it?

[hnguy71]

@rodneyc8063_1 , typically I would combine files that are like for like within the same folder. You could possibly explore the data and group them by specific folders.

However, if you know that in each of the CSV files you're picking up only the required fields, it's very possible to just have them dumped into one folder with the following transformation logic within the helper query:

1. Select all files in folder

2. Promote header. Remove fixed column evaluation at your source step

3. Select only required columns. Add optional MissingField.Ignore

let
    // Remove fixed column criteria
    Source = Csv.Document(Parameter1,[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    // Go ahead and promote all headers
    PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    // Select columns. Ensure you add the optional MissingField.Ignore
    SelectRequiredColumns = Table.SelectColumns(PromoteHeaders,{"Column1", "Column2", "Column3", "Column4"}, MissingField.Ignore)
in
    SelectRequiredColumns

As you can see, in this sample, I've asked for 4 columns but since only 2 exists, only 2 is returned.

And when I expand to combine all the CSV files, in one file I have two additional columns and it'll pick it up as well.

[rodneyc8063_1]

Hi @hnguy71 

This is definitely good to know and a neat trick!

Hm hopefully I didnt misunderstand your trick, but it sounds like "combining files within a folder" usually is for files that are like for like?

In my case for example I have a CSV file of name, then another CSV file of address, then another of telephone number etc. So each CSV file is a table in itself with unrelated columns.

I was hoping that there would be something where I could just select all the files and then let Power Query grab everything and load it all vs having to open each file and load it individually.

Not sure if something like this exists? 

[hnguy71 · Accepted solution]

@rodneyc8063_1 , if they have nothing in common with each other, unfortunately you'll have to transform them all separately. =(

[rodneyc8063_1]

Thanks for confirming this sad piece of news 😞

Also thank you for the neat trick, I will be sure to keep it in mind if I do need to do a mass import of similar files! 🙂