Red Gig

In a recent post, I showed you how to connect to multiple files in a folder . 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. 1. Open PowerQuery and use the 'get data' function to access the 'More…' option 2. Select the 'Folder' option and click 'Connect' 3. In the next window, click 'browse' to locate the folder that contains the file you're attempting to connect to then click 'OK' 4. 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 to work with the metadata from the

A recent discussion on R/Excel  " What is the most efficient way to insert a similar chart in several pre-existing worksheets of a workbook while making sure that the data source of the chart refers to the worksheet where it has been inserted?"  initiated a conversation about the different methods of displaying data from multiple sheets which i ncluded a question around working with data on multiple sheets that needed to be summarised on a chart that was identical on each sheet. As with most things with Excel, there are a number of methods that would achieve this result, I believe the quickest and most efficient is to use Power Query to combine the data and then display the output as a pivot table/chart with a slicer to allow the user to select each source as required. I realise this isn't providing a separate sheet for each data source however, assuming there are no restrictions on the visibility of the data, this is a 1-page solution that contains all the required data