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 process required to combine multiple sheets into one single table using PowerQuery. Shortly after posting this, the Power Query guru Wyn Hopkins pointed out an alternative method that makes the process more efficient which I wasn’t aware of. Note - If you've not checked out Wyn's PowerQuery content you're missing out, check out his YouTube Channel here and his podcast 'Power Query Magic' which is available on all good podcast platforms. When connecting to multiple sources in PowerQuery, you'll often be presented with the option to combine files. The Content column below displays the combine icon which consists of two down arrows and a single line across the bottom of the button. Clicking the 'combine files' button presents the 'Combine Files' window which provides a number of options. At first glance, it might seem that combining both sheets in the example below is not possible as there is no way of tickin

Most of the time data will need to be imported into the data model from a source so that the connection is 'live' and can be updated by refreshing the source data. It is also possible to enter data straight into the data model through the 'Enter Data' function. This function allows data to be typed or cut & pasted into a table. This way of entering data can be used to hard code any lookup tables that might currently be Excel files. If the data is hard coded in the model using the process below then the risk of links to files and filenames is mitigated. Here are the steps required to enter data directly into PowerQuery In the ribbon under 'Home' click the 'Enter Data' button to display the 'Create Table' window Enter the data either by typing it in or cut and paste from another source. Be mindful of the header row to make sure if you have headers, the headers are pasted into the header row. Columns can be inserted or deleted by right clicking

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