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