Tables for each file in folder, load header names from separate file

[MrSplinter]

I have the following files:

- A folder with multiple .CSV-files. Each file might have a different number of columns. The header names are missing.

- A .CSV-files in this folder called Index.csv, which contains the names of the .csv-files and a row for each header-name (plus alias).

The number of files and their names might vary. The Index.csv always contains the names of all files and their headers.

What I want:
- Load all the files in the folder
- Create a table for each separate file

- Load the correct headers names for each table from the Index.csv file

- The solution should still work if the number of files and their names change

Is this possible in Power BI?

I could write a loop in Python, but that solution would require the client to locally install Python.

Actual data is confidential, so here are two examples with fake data but identical structure:

Example of the Index.csv file:

Filename	ColumnName	ColumnAlias
City.csv	PostalCode	PC
City.csv	Longitude	Long
City.csv	Latitude	Lat
Inhabitants.csv	City	City
Inhabitants.csv	Number	No

Example of a random file (City.csv):

London	52.8484	5.5404
Berlin	49.3030	4.3030

[v-jingzhang · Accepted solution]

Hi @MrSplinter

I'm afraid this step "- Create a table for each separate file" cannot be done with Power Query. Power Query cannot create queries according to file names automatically. Queries need to be created by you manually. So the number of queries is fixed before any refresh. 

The closest result I can achieve with Power Query is like below. I can get the table you want for every file row. However, I'm not able to split this column into multiple queries (tables) automatically as Power Query doesn't support that. I have attached the sample file for your reference. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!