Career

Excel Tips: Power Query Part III - Creating a table from a flat file

September 9, 2021

5 min read

Welcome to another entry in our series of Dr. Wayne Winston’s best Excel tips! I’m Wayne Winston, Becker’s resident expert on all things Microsoft Excel. Let’s learn another helpful Excel skill that can make your accounting and finance work a bit easier.

Last month, we showed you how to use Power Query to transform data into a flat table, where each piece of information lives in a separate row. Even better, when we added a new entry to our table, a simple refresh ensured that the new entry was incorporated into our flat file. This month, we will show you how to use Power Query to transform a flat file into a tabular format.

To start, copy and paste this link into a new browser tab to download the Excel workbook and follow along.

Each row of the table contains the product sold, the month in which it was sold, and the number of units sold during the month.

Figure 1: A flat file

A flat Excel file

Our goal is to use the Power Query tool to rearrange this data into a table format, as you can see in this workbook link.

Figure 2: Sales in a table format

Sales in a table format

To create this table format, proceed as follows in the first workbook:

1. Select the data in the range C1:E36 and press Ctrl+ T to convert the selected range to an Excel table.

2. From the Data tab, in the Get & Transform Data Group, click From Table/Range (this option may also be listed as “From Sheet,” depending on how updated your Office 365 is.)

3. The Power Query Editor window will open. Hold down Shift to select both the Attribute and Value columns.

4. In the Any Column group on the Transform tab, click the Pivot Column button.

5. The Pivot Column dialog box will open. Leave the default settings as is and click OK.

6. Click the Home table in the Power Query Editor window.

7. Select the product column. Then, click the A-Z button in the Sort group on the Home tab.

8. In the Power Query Editor window’s Home tab, click the Close & Load button in the Close group. Alternatively, open the File menu and choose Close and Load.

9. Add a new entry to your source data for 25 units of soda in January. Then, click into the table and choose Refresh.

As shown in Figure 2, your data is now summarized in a table format, with the Product column sorted in alphabetical order. And of course, our new data is now incorporated in the table.

Learn more with Excel CPE

Excel offers a wide variety of functions, formulas, and automation that can save you hours of work while improving accuracy and outcomes. To help you make the most out of this software, we offer a wide variety of CPE courses to support your learning!

Check out Becker's wide range of CPE courses that teach you to make the most of this powerful tool:

These and many more Excel-focused, CPE credit-earning courses are included in Becker's Prime CPE subscription. Sign up now for 12 months of access to over 1700 on-demand, webcast, and podcast CPE courses!