How to access and combine the files in a Sharepoint Folder using PowerQuery

In a recent blog post, I described the steps required to connect PowerQuery to a folder on a local drive using the Folder.Files function in M. The Sharepoint.Files function uses the same process to connect to folders on Sharepoint.

In this example, I've created a sample file and created a number of duplicates of the file which I've saved in a folder on sharepoint. 

Once you have something similar the process you need to carry out is as follows:

• Open PowerBI or Excel, both of which contain Power Query. I'll be using PowerBI for this demo.
• Click on the button labelled 'Get Data', then 'more' and then when the following window appears, select 'Sharepoint Folder' and then click 'Connect'

• Then in the next window, enter the site URL (not the folder URL) which contains the files you'd like to combine then click 'ok in the window shown below

• The following window will display all of the files that are on the Sharepoint site with some buttons at the bottom of the screen to load the data in a few different ways. 

• Click 'Transform Data' to load the list of files into a query which will display a screen similar to the one below. 

• As you can see from the list above, this site contains a number of different types of files and some that we would like to exclude from the import process. First we'll filter the file type to list only CSV files and remove any files that arent of the format we wish to combine. To do this, select the drop down in the 'Extension' column and ensure only CSV is selected.

• Then we'll remove the 2 files that appear to be different by filtering the 'Name' column in the same way we filtered the 'Extension' Column

• Now we have a list containing only the files we wish to combine we can now click the 'Combine Files' icon in the 'Content' header or select the 'Content' column and click 'Combine Files' in the Home ribbon.
• The next screen confirms the settings to import a file. If this is CSV the default is usually sufficient. There is the option to 'Skip files with errors' which is unticked by default. If there is a change future files have errors, ticking this will exclude them and allow the query to be loaded without presenting an error to the user.

• Click 'OK' to combine the files
• Once the above sequence has completed, the combined data will be displayed as a single query which can then be loaded to the data model.

• In this example, the header row hasn't been configured correctly as the CSV file has been loaded with the first row as the first row of data. This can be addressed in a couple of ways. The recommended method is to modify the helper query titled 'Transform Sample File' to 'Use First Row as Headers' the alternative method is to apply the same 'Use First Row as Headers' transformation in the combined query (here titled 'Query1') but this also requires each subsequent header row to be filtered. Although this achieves the same result it creates 2 additional lines of code so its less elegant than applying the transformation to the 'Transform Sample File' query. 

• Rename the query as required by clicking on its name and pressing F2 and then load the data by clicking 'Close and Apply' (or close and load if you're using Excel).

Now you can add files to the folder on SharePoint and update the data in the data model with a single click of the 'Refresh' button.