Power Query: It’s Good to Share(Point) Part 5
23 October 2024
Welcome to our Power Query blog. This week, I complete the extraction of data from a folder on SharePoint.
In this series, I am considering how to access data on SharePoint. SharePoint is used by many companies to store organised data which may then be accessed and shared throughout the organisation. I will be starting with a single file and then move on to consider combining data in a SharePoint folder.
The data for this series will come from the following SharePoint folder:
Regular readers will be familiar with the expense data in the form of CSV files, I’m sure!
In Part 3, I completed the task of extracting a single CSV file from SharePoint into Power Query.
Last time, I used the ‘From SharePoint Folder’ option to combine all the CSVs in the SharePoint folder
I filtered Folder Path to select only the data in the required folder:
These are the files in my folder, and I may now make the solution robust by selecting the Extension column and transforming it to UPPERCASE in the Transform section of the right-click menu:
Next, I add to ‘Text Filters’ from the filter button dropdown:
I select only those files with Extension ‘.CSV’.
I may now select the Content column and choose to remove the other columns:
Finally, I use the ‘Combine Files’ icon next to the column heading to combine the data. Whilst this takes longer than combining local files, as Power Query accesses the SharePoint folder, the process is the same. The ‘Combine Files’ dialog appears:
When I click ‘OK’, the helper queries are created and the data is combined in the original query:
The data may then be transformed in the usual way.
Accessing data in SharePoint is simple once the method to access the data has been carried out correctly. The process may be slower, depending upon the SharePoint site being used, but using the URL to access the data means that the same current data may be accessed by other users in the organisation when using the same workbook. I was also able to access the data whilst I had one of the CSV files open in Excel, but this may vary depending upon how the SharePoint site has been set up.
Come back next time for more ways to use Power Query!
