Demand planners are often inundated with data from numerous sources. In order to develop good forecasting models for a product or service, you have to understand both local and global trends, historical contexts, in addition to the standard in-house sales and finance data, of course.

Consequently, it’s recommended that you utilize Power Query, in order to import and manage data from both internal and external sources, when using Excel.

This data could be stored in different formats, for example you could have data in Access databases, in pdf files or comma-delimited files that you would like to import and manage in Excel.

In this tutorial, I will cover how to use Power Query to import the relevant data we need, from an Access database into Microsoft Excel. I will also go over a simple way, to transform the data we imported, in Power Query.

Power Query is a popular automation tool available in Microsoft Excel 2010 and other later versions of Excel.

The Data to Information Process

Part of a demand planner’s main role is to provide in-depth knowledge, on every aspect of the organization’s core performance indicators, to accurately predict customer demand for the service or product that the organization provides.

During the seminars I give, I often explain how this ties into the data to information process. Being a supply chain finance consultant, I have to take raw data and ultimately deliver insightful information about the demand for a product or service.

So, in the next sections, we will focus on how we can use Power Query for Step 1 and Step 2 of the Data to Information Process.

You can read more about Forecasting and Planning in this post.

Importing Data from an Access Database Using Power Query

Microsoft Access is a relational desktop database application. Microsoft Access has the most scope in home and small business spheres, since it accommodates a limited amount of users and data requests.

Unlike SQL Server which is designed to handle thousands of users and many requests. In our source example, we have a folder that contains an Access database.

This database has the sales data of a hypothetical beverage company that sells coffee and tea. We have the annual sales data for the years: 2020, 2021 and 2022 stored in three different tables in the database.

So, to get the sales data for 2020, from the Access Database into Microsoft Excel, using Power Query follow these steps:

1. Open Microsoft Excel, create a blank workbook and select the Data Tab.

2. In the Get & Transform Data Group, select the Get Data option drop-down arrow.

3. Select From Database/From Microsoft Access Database.

4. We will navigate to the folder containing our database and select Import.

5. In the Navigator Pane, select the Table called SalesData2020T.

6. Select the drop-arrow next to Load and then select Load.

7. You should now see the table containing the sales data for 2020, imported into Excel.

Shaping the Data

So, now we will look at Step 2, of the Data to Information Process. In a real-world example, this could involve many different techniques including but not limited to trimming spaces, splitting columns, removing duplicates, creating custom columns, or spell-checking.

We can see in our dataset that we have the columns Units Sold and Unit Price. So, we want to create a Custom Column called Revenue using Power Query. This column will be the result of multiplying the Units Sold column by the Unit Price column.

To do this you will need to follow these steps:

1. Select any cell in the imported table, go to the Query Tab and then in the Edit Group, select Edit.

2. You should now see the Table in the Power Query Editor.

3. Go to the Add Column Tab, and in the General Group, choose Custom Column.

4. In the Custom Column Dialog Box, enter the name of the new column, which in this case is Revenue.

5. Insert the Units Sold Column into the Custom column formula, section by clicking on it in the right-hand panel and then clicking Insert.

6. Now type the * Operator and then insert the Unit Price Column in the same way as we did above.

7. Click the Ok button to see the Revenue column added.

8. Now go to the Home Tab, and in the Close Group, click on the drop-down arrow next to Close & Load and select Close & Load.

9. You should now see the table, with the custom column we created in the Power Query Editor, loaded in Excel.

Conclusion

In this tutorial, I went over how to use Power Query to import data from an Access database and how to create a custom column in the Power Query Editor.

It’s a good idea to leverage Power Query as much as possible, when you need to collate and organize data from different sources, in Excel.

Please let us know in the comments below, about your thoughts on Power Query and if you use this feature often.

A special thank you to Taryn Nefdt for collaborating on this article.