Category: Blog

Preparing data is probably the most critical success factor in data analysis.  If the data layout is wrong, you be continually fighting Excel; having to use advanced formulas, adding unnecessary columns or even duplicating data.  But if the data is in the right structure, Excel becomes easy.  I used to be amazed at people who Read More

The web is a vast place with so much information.  However, just because the data is there does not make it easy to get at  In this post, we’ll look at how we can load web data into Power Query.  While you might get excited at the thought of absorbing data from everywhere with the Read More

We’ve seen how to import external data from a single file, how to import all the files in a folder and how to import data from a Table / named range within the same workbook.  But what if we want to import ALL the data within the same workbook?  Well, that is where we are Read More

In the last post, we saw how to import all the files from a folder, which is a fantastic time-saving technique.  While focusing on this aspect, we skipped over another useful part of Power Query; which is being able to obtain a list of all the files in a folder, and sub-folders, along with the Read More

In this post, we are looking at using Power Query to import all the files in a folder.  We give Power Query a folder path, click a few buttons and it will import and combine all the files into a single table.  That’s powerful stuff, right!  If we want to add another file into the Read More

Data doesn’t always come in one single file, or from one download.  We often have to find a way to stitch multiple files together so that we can use it as if it were a single data source.  This might be cost center reports, monthly data extracts, product profiles, survey data, etc.  Basically, it applies Read More

We’ve looked at a few transformations already in this Power Query series, but now we are going much further and look at the transformations available in the main Power Query menus.  We can’t cover everything in this post; there are just too many options available.  Also, some of the more detailed transformations will be included Read More

As Power Query records the transformation steps, it includes many hardcoded values within the M code.  For example, if we filter a column to select all values greater than 50, the 50 will be a hardcoded value.  Or if we import a CSV file, the file path is hardcoded into the query. What if we Read More

In this part of the Power Query series, we will be taking a quick look at the Close & Load options.  So far, we have only used the default option which loads the output of the query into a new worksheet.  But there are plenty of other options we could use instead. We won’t get Read More

In this part of the Power Query series, we will look at how to edit queries.  We’ve seen a small piece of this in a previous post, but now we’ll go a lot deeper. Download the example files It may be tempting to just read the text below, but you will retain the information much Read More