How to Use Blank Query in Power BI

Diving into Power BI’s Power Query Editor often starts with connecting to a standard data source like an Excel file or a database. But what if you need to create something from scratch, build a custom function, or wrangle data in a way that the user interface doesn't easily support? That’s where the surprisingly powerful Blank Query comes in. This article walks you through exactly what a Blank Query is and how you can use it to build custom tables, create reusable functions, and perform advanced data transformations.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is a Blank Query, and Why Use It?

A Blank Query is exactly what it sounds like: a completely empty query in Power Query that isn’t connected to any data source. Think of it as a blank canvas. When you create one, you're not pulling data from a file or database, you're starting with nothing but an empty slate and the Power Query formula language (known as M code) at your fingertips.

While that might seem daunting, it opens up a world of possibilities that go beyond the point-and-click interface. You'd typically use a Blank Query when you need to:

• Create a Custom Table: Sometimes you need a small, static table within your report - maybe for a calendar, a list of parameters, or a custom sorting index. A Blank Query lets you write M code to generate that table right inside Power BI.
• Build Reusable Custom Functions: If you find yourself applying the same series of complex transformations over and over, you can write a custom function in a Blank Query. This turns a multi-step process into a single, reusable action you can apply to any column or table.
• Perform Complex Data Transformations: You can write intricate M code to connect to web APIs, parse complex data structures like JSON or XML, or sequence transformations with a level of control that the standard buttons can't offer.
• Manage Parameters: They're perfect for centralizing static values or variables (like an API key or base URL) that you want to reference in other queries, making your report easier to update later.

Essentially, a Blank Query is your gateway to manually scripting with M, giving you ultimate control over your data preparation process.

How to Create a Blank Query

Starting a Blank Query is a straightforward process. Follow these simple steps in Power BI Desktop:

1. From the Home ribbon, click on Get Data.
2. In the Get Data window, select Other from the list on the left.
3. Choose Blank Query from the list of options and click Connect.

This action will launch the Power Query Editor with a new query, likely named "Query1," in the Queries pane. The main window will be empty, and you'll see a formula bar at the top reading =Source. This is your starting point.

Now, let's put it to use.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Use Case #1: Create a Custom Table from Scratch

One of the most common and practical uses for a Blank Query is to manually create a table. Let's say we need a small table to categorize sales performance into different tiers like "Low," "Medium," and "High," with a minimum and maximum sales value for each.

Step 1: Open the Advanced Editor

With your new Blank Query selected, click on Advanced Editor in the Home tab of the Power Query ribbon. This opens a new window where you can write and edit M code directly.

Step 2: Write the M Code to Create the Table

Delete the default let Source = "" ... text. We're going to use the #table() constructor to build our table. This function takes two arguments: the list of column headers, and a list of lists representing the rows of data.

Enter the following code into the Advanced Editor:

let Source = #table( {"Tier", "Min Sales", "Max Sales"}, { {"Low", 0, 4999}, {"Medium", 5000, 14999}, {"High", 15000, 100000} } ) in Source

Step 3: Define Data Types

The table has been created, but Power BI is currently treating every column as a generic "any" type. It's a best practice to define your data types explicitly for better performance and to prevent errors.

Let's modify the M code to specify the data types using Table.TransformColumnTypes(). The "Tier" column should be text, while the sales columns should be integers.

Update your code in the Advanced Editor to look like this:

let // Step 1: Create the raw table structure and data Source = #table( {"Tier", "Min Sales", "Max Sales"}, { {"Low", 0, 4999}, {"Medium", 5000, 14999}, {"High", 15000, 100000} } ),

// Step 2: Set the correct data types for each column
#"Changed Type" = Table.TransformColumnTypes(Source,{
    {"Tier", type text}, 
    {"Min Sales", Int64.Type}, 
    {"Max Sales", Int64.Type}
})

in #"Changed Type"

Click "Done." You will now see your three-row table in the Power Query preview, and the column headers will have the correct data type icons (ABC for text, 123 for whole numbers).

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 4: Rename Your Query

Finally, rename your query from "Query1" to something descriptive, like "Sales Tiers," in the Query Settings pane on the right. Now you can load this table into your data model and use it to build relationships and DAX measures.

Use Case #2: Build a Reusable Custom Function

Let’s say you regularly need to clean up text data by removing extra spaces and converting it to proper case (making the first letter of each word capitalized). Instead of performing these two steps on every single text column you import, you can build one custom function to do both at once.

Step 1: Create a Blank Query for the Function

Start a new Blank Query and open the Advanced Editor.

Step 2: Write the M Code for the Function

A custom function in M code has a simple structure: you define input parameters in parentheses, followed by =>, and then an expression that uses those parameters to produce a result. For our text cleaning function, we only need one input parameter: the text value we want to clean.

Enter this code into the Advanced Editor:

let CleanTextFunction = (inputText as text) => let TrimmedText = Text.Trim(inputText), ProperCaseText = Text.Proper(TrimmedText) in ProperCaseText in CleanTextFunction

Code Breakdown:

• (inputText as text) => declares that our function accepts one parameter named inputText (which we explicitly type as text) and that the expression to calculate follows.
• Text.Trim() removes any leading or trailing whitespace.
• Text.Proper() converts the text to Proper Case.
• The function returns the final result, ProperCaseText.

Step 3: Name and Save the Function

Click Done. Power Query will recognize that you've written a function. Instead of a data preview, you'll see a simple user interface asking for the inputText parameter. In the Queries pane, the query icon will change to fx.

Rename this query to something meaningful, like fn_CleanTextInput (the "fn" prefix is a common convention for functions).

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 4: Use (or "Invoke") the Custom Function

Now, let's use our new tool. Navigate to another query in your Power Query Editor that has a column of messy text data (e.g., a "Product Name" column with inconsistent capitalization).

1. Select the query containing the column you want to clean.
2. Go to the Add Column tab on the Power Query ribbon.
3. Click Invoke Custom Function.
4. In the dialog that appears:

Click OK. Power BI will create a new column and apply your custom function to every single row in your selected column, giving you perfectly formatted text in one step!

Tips and Best Practices for Blank Queries

Working directly with M code can be tricky, but these tips will make the process much smoother.

• Use Comments Liberally: Just like with any other coding language, document what you're doing. Use // for a single-line comment or /* ... */ for a multi-line block. This will help you (and your teammates) understand your logic months later.
• Follow a Naming Convention: Be consistent with how you name your queries. For example, use prefixes like fn_ for functions, tbl_ for manually created tables, or p_ for parameters to keep things organized.
• Lean on the UI to Learn M Code: Power Query is great because it writes M code for you behind the scenes for every button you click. To learn how a particular transformation works, perform it using the interface first, then go to the Advanced Editor to see the code it generated. This is an excellent way to learn M syntax without a textbook.
• Break Down Complex Logic: The let ... in structure in M is powerful. You can define multiple intermediate steps, each building on the last. This makes complex queries far easier to read, debug, and maintain than one giant, nested formula.

Final Thoughts

Blank Queries transform you from a passenger in the graphical interface to the driver with full control over the engine. By starting with a blank slate, you’ve learned how to create bespoke data tables, build powerful, reusable functions, and master advanced transformations by writing M code directly. It’s a key skill for taking your data preparation abilities in Power BI to the next level.

While mastering Power Query's M code is a huge advantage for in-depth data work, the reality is that not everyone has the time to learn another scripting language. Often, marketers and sales leaders just need immediate answers from their scattered data sources without navigating a steep learning curve. This is why we built Graphed . Our platform automates the entire process by connecting to your tools like Google Analytics, Shopify, and Salesforce and letting you build dashboards using simple spoken language. Instead of writing custom code, you can just ask, "Create a dashboard comparing Facebook Ads spend vs revenue by campaign," and get a live, interactive report in seconds.