How to create and populate a table in Microsoft Excel’s Power Query

You can enter data into a Microsoft Excel sheet or run a bit of M code in Microsoft Excel Power Query when you need to create a table.

Updating data in a Microsoft Excel workbook is common, but you will run into cases where you can replace manual input by running a bit of M code in Power Query. This task is best when creating static tables. For instance, you might reference a table of commission percentages to calculate commission amounts. Those values will rarely change. Using code in Power Query is a good alternative when exporting that data to another app.

In this tutorial, I’ll show you how to generate and populate a static commission table in Power Query using M code. The dataset will include a sales benchmark value and a corresponding percentage rate. Once the table is in Excel, you can reference the values to calculate commission amounts.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)

Initially, writing the M code isn’t any quicker than entering the values manually. Once you have the query in Power Query, you can alter the M code as needed and quickly regenerate the entire dataset at any time or even call it from another app.

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions back to Excel 2010.

You can download the Microsoft Excel demo file for this tutorial.

How to open a blank query in Power Query

Because we’re creating and populating the data set in Power Query, we need to open a blank query. To do so, click the Data tab and then choose Launch Power Query Editor from the Get Data dropdown in the Get & Transform Data group.

To add a blank query, right-click in the query panel (to the left) and choose New Query. In the resulting submenu, choose Other Sources. Then, in the next submenu, choose Blank Query as shown in Figure A.

Figure A

Power Query will open the formula bar and name the query Query1, which you can change to Commission Rates. Simply double-click the name and enter the new name from the keyboard.

How to generate a table using #table in Power Query

Few of the features are available because there’s no data to manipulate. Instead, we’ll use M code to generate the dataset. On the Home tab, click Advanced Query Editor in the Query group to open a blank window. If you like, click the Display Options dropdown and choose Display Line Numbers from the dropdown. This isn’t necessary, but I’ll refer to line numbers later.

Enter the code below as shown in Figure B. If you make any mistakes, the syntax editor at the bottom will display information. As you can see, there are no syntax errors, so click Done to generate the data set shown in Figure C.

#table

(

    type table

    [

       #"Benchmark" = Int64.Type,

       #"Rate" = Number.Type

    ],

    {

        {0,00},

        {800,.002},

        {10000,.003},

        {12000,.0035},

        {15000,.004},

        {20000,.055}

    }

)

Figure B

Figure C

The code is a simple version of an M #table function; a comprehensive review of #table is well beyond the scope of this article. Lines 1 through 3 execute the table function. Lines 4 through 7 define the columns by name, Benchmark and Rate, and data type. Lines 8 through 15 specify the values for both columns. The first column generates the benchmark values and the second generates the corresponding rates. Line 16 completes the function by closing the first parenthesis in line 2.

How to load the data into Excel

After generating the new dataset, you can load it back into Excel. To do so, click the Close & Load option in the Close group on the Home tab. Figure D shows the new dataset in use. As you can see, rates below $20,000 are low, but once a sale hits that benchmark, commissions are much higher.

Figure D

Admittedly, in such a simple example, using Power Query seems like overkill. Initially, you don’t seem to be saving any time or effort. However, imagine that you’re working with a much larger dataset and the convenience becomes clearer.

Once you have the original query in Power Query, you can quickly alter the M code and regenerate the data set at any time by creating a function to pass new values via parameters. This task is simple, but it can be part of a much larger process.