Leveraging Power Query to harness your data.

The Power Query Add-On provides power capabilities to connect to multiple data sources, import data, query and transform data.

Power Query allows you to load millions of rows into Excel’s Data Model which keeps the data in memory instead of numerous worksheets. This enormous data storage allows analysis without the limitation of worksheets.

In this example, I’ll illustrate loading test data and the query editor.

I’ll begin with example store data which contains over 9,000 rows.

Since Power Query allows you to load records into memory instead of using physical worksheets, you can easily load millions of records without bogging down your computer.

To load the test data into memory, I’ll load the data from the “Data” tab’s “New Query function.”

Once the file loads, you will be presented with available worksheets (left). When you click a worksheet, a preview of the data will display (right).

At the bottom of the popup window, select “Load To.”

On the next window, select “Only Create Connection” and “Add this data to the Data Model.”
Then, press “Load.”

A new window will appear showing query ready for you to execute to see your records. Remember, you used “Load To” to store the data in memory instead of physically in a worksheet.
Each query will display in this area along with a record count.
Also, you may mouse-over the query to see a preview of the data.

Also, the bottom of the preview windows presents additional options regarding the query.

Next, I’ll double-click the query to load the data into a Query Editor.
As you can see, the Query Editor presents numerous options to transform data which I’ll cover in future articles.