Red Gig

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

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

When working with text in Excel its often useful to combine the text from two or more cells into a single cell. This can be done with a couple of different methods Method 1 - using the Concatenate function I've previously blogged about the basics of this function in this post . Here we'll look at the function in a bit more detail and some common use cases. Almost all people I've helped with Excel aren't familiar with the word 'Concatenate', its definition is "link (things) together in a chain or series." which is exactly what the concatenate function does. Its simplest use case would be, = Concatenate (A1, A2) This would combine the values in A1 and A2 into a single value which would be displayed in the cell in which the function is placed, lets look at a real example Here we have a list of first names and last names and we'd like to combine them into another column titled 'Full Name'. We'll begin by using the Concatenate function and ...