Red Gig

  In a recent post, I showed you how to connect to a folder and combine multiple files on Sharepoint . This is a similar method but it connects to just one file that meets a specific rule or criteria such as the latest file with a filename beginning with a certain string. A common example of this would be where a system automatically generates a file and saves it in a folder then there is a need to analyse just the latest file. Open PowerQuery and use the 'get data' function to access the 'More…' option Select the 'Sharepoint Folder' option and click 'Connect' In the next window, paste the URL of the sharepoint site (not the folder) that contains the file you're attempting to connect to then click 'OK' In the next window, click 'Transform Data' Note, clicking 'Combine and Transform Data' will initiate the process of combining the files. As we need to apply a transformation to the list before accessing the file, we'll need ...

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 a...