Loading Our First Set of Data

Loading Our First Set of Data

Previous Session Summary

In the previous lesson, we looked at why PowerQuery can be one of the fastest and cheapest ways to making a Finance Team in particular more efficient and more accurate.
Almost every task that a Finance Team undertakes is repetitive, and those tasks need data. Before those tasks can be performed, the data needs to be cleaned and spliced together. That takes time and can result in errors.
PowerQuery eliminates that time and that potential for errors.

Lesson Synopsis

So let’s get straight into how to use PowerQuery and Stage 1 – Connecting PowerQuery to our dataset.

We’re starting slowly, and in this lesson we’re going to connect a dataset which is in our current Excel workbook. The data is a really simple set of Purchase Order information that we could run out of any accounting system but has a few rows that we can remove using PowerQuery.

Once we’ve connected our dataset, we’ll get into the PowerQuery editor and have a look around, ready for Stage 2 – Transforming our Dataset.

Resource Listing

The Video

Play Video

Lesson Notes

The dataset that we’re working with shows Purchase Orders that have been created in a fictitious company. It’s got columns showing Vendors, PO numbers, Order Dates, the products that our fictitious company has ordered, the value of the products and the date that the products were ordered.

From there, we’re going to take the data and put it into the PowerQuery editor, creating our first query and going into the PowerQuery editor for the first time.

In the PowerQuery editor then we’ve got a number of components:

.... and a little more!

The Preview Screen – The Preview Screen only loads 1000 rows at once. If you want to scroll through more rows then use the scroll bar at the side and scroll to the bottom of the preview screen. However after the first 1000 rows, PowerQuery only downloads from the source file 50 rows at a time. This keeps PowerQuery running quickly.