How to Create a Table in Power BI Query Editor

Sometimes the data you need doesn’t live in a database or a spreadsheet, you just need to create it from scratch. Whether you're building a simple lookup table or setting up a template for data modeling, you can create a custom table directly within Power BI’s Power Query Editor. This tutorial will walk you through exactly how to do it using two different methods.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Create a Table in Power Query?

While Power BI is designed to connect to existing data sources, there are plenty of practical situations where creating a table manually inside the Power Query Editor makes perfect sense. It's a skill that can save you a surprising amount of time and effort.

Here are a few common scenarios where you might need this:

• Creating Lookup Tables: These are small, simple tables used to add more context to your data model. For example, a table that maps a status code (like 1, 2, 3) to a human-readable status ("New," "In Progress," "Completed").
• Mapping Old Data to New Data: You might create a table to map old product SKUs to new ones, or to group various text spellings (e.g., "USA," "U.S.", "United States") into one clean value ("USA").
• Building Scaffolding Tables: These tables help shape your data model. A classic example is a calendar (or date) table that contains every date in a year, which is essential for time-intelligence calculations. You could also create tables for number sequences or time intervals.
• Setting Report Parameters: You might create a small table of values to use in a slicer or filter, allowing users to select dynamic parameters like "Top 5" or "Top 10" to change what a visual displays.
• Placeholder Data for Development: Sometimes you need to build a report model before the final data source is ready. Creating a placeholder table with the correct column names and data types allows you to build out your relationships and visuals ahead of time.

In all these cases, creating the table directly in Power BI keeps everything tidy and self-contained within a single file, rather than managing yet another external spreadsheet.

First, Open the Power Query Editor

Before we can create a table, we need to get into the right part of Power BI. The Power Query Editor is the data transformation engine where all the data shaping and cleaning happens.

Follow these steps to open it:

1. Open a new or existing Power BI Desktop file.
2. On the Home tab of the ribbon at the top, find the "Queries" section.
3. Click the Transform data button.

This will launch a new window: the Power Query Editor. This is your workshop for everything related to preparing your data before it hits the main Power BI report canvas. Any new tables we create will appear as new queries in the pane on the left side of this window.

Method 1: The Quick and Easy Way with "Enter Data"

For small, simple tables, the "Enter Data" feature is the most straightforward approach. It provides a simple grid interface, much like a tiny spreadsheet, where you can type your data directly.

Let's create a lookup table that assigns a name to a priority level.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Guide to "Enter Data"

1. Open the Create Table Window: In the Power Query Editor, make sure you're on the Home tab. In the "New Query" section, click the Enter Data icon.

2. Build Your Table: A "Create Table" dialog box will appear. Here's how to use it:

• Add Columns: Click the asterisk symbol (*) in the column header to add a new column to the right.
• Rename Columns: The default names are a bit boring (Column1, Column2, etc.). Double-click a column header to rename it. For our example, let's rename Column1 to PriorityID and Column2 to PriorityLevel.
• Enter Your Data: Click into the cells and type your data. Pressing the Tab key moves you to the cell on the right, and Enter moves you down a row.

Let's add three rows of data:

• Row 1: 1 in PriorityID, High in PriorityLevel
• Row 2: 2 in PriorityID, Medium in PriorityLevel
• Row 3: 3 in PriorityID, Low in PriorityLevel

3. Name Your Table: At the bottom of the window, you'll see a field called "Name." It's essential to give your table a descriptive name. Let's call this one PriorityMapping. Giving it a clear name will make it much easier to find later in your data model.

4. Click OK: Once you're finished, click "OK".

Your new table will now appear as a query in the "Queries" list on the left. Power Query automatically records the steps you took on the right under "Applied Steps," showing a "Source" step where the data was created and a "Changed Type" step where it tried to guess the correct data types.

Pro Tip: Always double-check this "Changed Type" step. Power Query is good but not perfect. Click the icon next to each column header and make sure the data type is correct. In our example, PriorityID should be a Whole Number, and PriorityLevel should be Text.

Method 2: A More Powerful Approach with M Code

While "Enter Data" is great for quick entries, it becomes clumsy if your table has more than a few rows. For greater control, flexibility, and scalability, you can create a table directly using Power Query's scripting language, known as M.

Don't be intimidated by the term "code"! The syntax for creating a basic table is very straightforward and easy to copy and modify.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Section 1: Creating a Table from Lists with the #table() Function

This function lets you define the column headers and then list out the rows of data. It's clearer, more portable, and easier to edit for larger tables than clicking around in the "Enter Data" UI.

Here’s how to do it:

1. Start with a Blank Query: In the Power Query Editor, go to the Home tab, click New Source, and select Blank Query.

2. Open the Advanced Editor: With your new empty query selected, go to the View tab and click on Advanced Editor. This is where you can write or paste M code directly.

3. Write the M Code: Replace the existing code with the following snippet to create the same priority mapping table we made earlier:

let
    Source = #table(
        type table [PriorityID = Int64.Type, PriorityLevel = Text.Type], // Optional: Defines column headers and data types
        {
            {1, "High"},
            {2, "Medium"},
            {3, "Low"}
        }
    )
in
    Source

Let's break down this code:

• #table(...) is the M function to create a table.
• The first part in the parentheses, type table [...], actually defines our column headers and their data types. Setting the types here is much more efficient than relying on a separate "Changed Type" step.
• The second part is a list of lists, where each inner list represents a row of data, enclosed in curly braces {}. The values in each row correspond to the order of the column headers.

4. Click "Done": After pasting the code, click "Done." Your table will appear instantly. Don't forget to rename your query from Query1 to something meaningful, like PriorityMapping_M, in the query settings pane on the right.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Section 2: Generating Data Series with List Functions

Another powerful use for M code is generating series of data, which is perfect for creating calendar tables or numeric sequences.

Let’s create a simple table containing numbers 1 through 100.

1. Create a Blank Query: Just like before, start by creating a new Blank Query (Home > New Source > Blank Query).

2. Generate a List of Numbers: In the formula bar at the top (if you don't see it, go to the View tab and check the Formula Bar box), type the following and press Enter:

= List.Numbers(1, 100)

This code tells Power Query to generate a list of numbers, starting at 1 and creating a total of 100 numbers. You now have a list, but it's not yet a table.

3. Convert the List to a Table: With your list selected, a special "List Tools" context ribbon appears at the top. In the Transform section of this ribbon, click the To Table button.

A small dialog box will pop up. You can just click "OK" with the default settings.

4. Polish Your New Table: Power Query has just converted your list into a single-column table. To finish, you just need to:

• Rename the Column: Right-click the header Column1 and select "Rename." Let’s call it Value.
• Set the Data Type: Click the "ABC" icon in the column header and change it to Whole Number.

You can use this same technique with other List functions like List.Dates() to very quickly generate a date table, which is a foundational element in nearly every serious Power BI report.

Final Thoughts

Knowing how to create tables directly in Power Query is a fundamental skill. Using "Enter Data" is perfect for quick, disposable tables, while writing M code provides a more robust and scalable solution for lookup tables and dynamically generated data series. Adding these manual tables expands your ability to build more organized, flexible, and powerful BI models.

As you've seen, getting your data shaped perfectly in a tool like Power BI involves many steps, from connecting sources to transforming data and building relationships. We believe getting insights from your data shouldn't be so complex. We built Graphed to remove these barriers, allowing you to connect all your sales and marketing data in one place and simply ask for the reports you need in plain English. Instead of learning M code or navigating menus, you can get real-time dashboards delivered in seconds.