Category: Power Query

Looking up data from a table or worksheet is probably the most common activity undertaken by Excel users to create reports.  Learning to use VLOOKUP for many is their first taste of Excel’s power.  But what about when using Power Query?  There isn’t a VLOOKUP function, so how can we lookup data from another table? Read More

As we continue through this Power Query series, we have now reached a common and frustrating data format; data stacked in a column.  In this post, we will investigate one basic method for unstacking data. While the example we are looking at below would most likely be created by a person who didn’t know how Read More

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