Combining multiple files 169

Figure 5.27 – Null values for non-matching columns

You can append more than two tables and apply the same logic to a wider number of queries.

Combining multiple files

While working with your data, you may need to automatically combine multiple files. You could use the append transformation method, but if you imagine a use case where there are files loaded into a folder with a defined frequency and you need to see the new data coming at each refresh, it is clear that an alternative method is needed. In this recipe, you will see how to connect and combine multiple files with just a few clicks, regardless of how many there are in the folder.

170 Combining Queries for Efficiency

Getting ready

For this recipe, to test different types of file connectors, you need to download a CSVFiles folder containing CSV files, as shown in the following screenshot:

Figure 5.28 – Local folder with CSV files

In this example, I will refer to the following path: C:\Data\CSVFiles.

How to do it…

Open the Power BI Desktop application to perform the following steps:

1.Go to Get Data, then click on the Folder connector. You can directly enter your folder path or click on Browse and select the folder from the usual browsing section of your machine. Then, click on OK.

2.You will see the following window with a list of files contained in the folder:

Combining multiple files 171

Figure 5.29 – How files from the folder are displayed

3. Click on Transform Data and you will see the following columns:

Figure 5.30 – List of files in Power Query view

172Combining Queries for Efficiency

4.Click on the Combine icon, which you will find in the left corner of the Content column, as shown in the following screenshot:

Figure 5.31 – Combine icon

5.The Combine Files window will pop up. From here, you can define which file to use in the Sample File field and define the File Origin, Delimiter, and Data Type Detection fields. Leave the detected values you see for each field and click on OK, as seen in the following screenshot:

Figure 5.32 – Combine Files window