In R you’ll use rbind, in Pandas you’ll use append, in SQL you’ll use UNION. But how do you append files in PowerQuery? In this example I will combine multiple files that contain data for every month of 2019. For convenience, I use the refundable pharmaceutical products in Belgium.
Before we get started, you should make sure to take the following steps:
- If your files don’t have a date field, make sure that your filenames contain dates, or months: just something that will help you identify where the data came from when we have combined all the data. We will use these dates as the value of a specific date column.
- Put all the files in the same folder (and remove all files you don’t need to be combined).
Once that is done, you load the data in Excel as follows: Data > Get Data > From File > From Folder.
A window will open that lists all kinds of properties of the files that are in the folder. Now combine the files and get ready to transform some data by clicking Combine > Combine & Transform Data. If you’re loading a database (like me) you’ll get an intermediary window where you can select the table you want to combine.
As you can see, a column has been created: Source.Name. That column contains the filename where each row has been loaded from. This filename isn’t really handy. However, the reason I asked you to have the date in each filename, is so we can change it into a proper date field.
Now, go to Transform > Replace Values. In the Value To Find field, you should remove the parts of your filename, so you’ll just be left with the date value. In my example, I remove “RIZIV_INAMI_”. I take exactly the same steps to remove “.accdb”.
Next, I change the date type of the column to a date, as follows:
And finally, I also change the column name in something useful: “DATE”.
Congratulations, you now combined multiple files and you can load it to your workbook!