In this post we cover how to work with multiple files of type CSV when loading into Power Query. Very often we have a set of data provided in a specific format on a daily / weekly basis on which we want to perform the analysis using PowerBI. Combing the different files into a single dataset is one of the most common tasks that is performed as part of ETL and here we explore the different ways of achieving it.
When working with large files or many small files the issue we face most often is the fact that the combined size of the dataset might exceed the capability of PowerBI in Excel or the PowerBI cloud service. In such cases it is often better to load the data into a single table in a database and perform the remaining tasks by connecting to the table in the database instead. The below link has a video on how to import data into a database.
When working with multiple small files we can also import the data into Power Query directly as shown below
Launch Power Query within Excel
Click the Option “From Folder” and navigate to the folder that contains the files you would like to import. The below screenshot will appear with a list of files found in the folder
Click Load , one would think this would be enough but actually if you look closely at the text below “From Folder” you will see it only imports the meta data. The next step is to open the Power Query Editor.
Here we see the same meta data as well as other information as shown in the screenshot below, the column we are interested in is Content which is usually the first column in the list. If you notice the column name there is a double down arrow icon next to the word Content. We need to click this in order to explore the contents of the file.
At which point the file content are displayed and our work is done. In the screenshot below you will notice I am loading data from different types of files with different number of columns. Ideally in the real world you would you a folder containing all files in the same format.