How to Write a Query in Power BI

Writing a "query" in Power BI isn't just one thing, it's a process that happens in two different, powerful worlds. This article will walk you through both: shaping your raw data using the Power Query Editor and then analyzing it with DAX formulas to create dynamic visuals.

Graphed

Still Building Reports Manually?

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

Watch the Demo

First, What Does "Writing a Query" Mean in Power BI?

Unlike traditional databases where "query" almost always means writing SQL, Power BI has two distinct environments for fetching and manipulating data. Understanding when to use which is the first step to becoming proficient.

• Power Query (using the M language): This is your data transformation engine. You use it before your data is loaded into the Power BI report. It’s perfect for cleaning, shaping, filtering, merging, and preparing your datasets. The best part? Most of the time, you don't even have to write code, a user-friendly interface writes it for you.
• DAX (Data Analysis Expressions): This is your analysis and calculation language. You use it after the data is loaded into your data model. DAX is used to create calculated columns, measures, and tables that bring your data to life in reports and dashboards.

Think of it this way: Power Query builds the clean, perfect set of Lego bricks. DAX is what you use to build incredible models with those bricks.

Part 1: Shaping Data with the Power Query Editor

The Power Query Editor is where all data transformation begins. Any file you import or data source you connect to will pass through here first. Let's walk through how to use its visual tools to write a query without typing any code.

Step 1: Get into the Power Query Editor

Once you’ve loaded a data source (e.g., via Get Data > Excel workbook), the most reliable way to open the editor is by clicking the Transform Data button on the Home ribbon. This opens a new window dedicated entirely to data preparation.

Step 2: A Quick Tour of the Interface

When the editor opens, you'll see a few key areas:

• The Ribbon: Just like other Microsoft products, it has tabs like "Home," "Transform," and "Add Column," full of buttons for performing actions.
• Queries Pane (Left): A list of all the queries, or tables, you are currently working on.
• Data View (Center): A preview of the data in your selected query. Here you can scroll through rows and columns.
• Applied Steps (Right): This is the most important part! It records every single transformation you make as a distinct step. You can click on any step to see what the data looked like at that point, which is amazing for debugging.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Perform Transformations (and Let Power BI Write M Code)

Let's imagine we've loaded a messy sales data file. Here are a few common cleaning steps you can perform with simple clicks.

1. Remove Unnecessary Columns

Your data likely has columns you don't need for your report. To remove them:

• Select one or more columns by holding down Ctrl and clicking the column headers.
• Right-click on one of the selected headers and choose Remove Columns.

Look over to the Applied Steps pane. A new step called "Removed Columns" has appeared. Power BI has just written the M language query for you in the background.

2. Filter Out Rows

What if you only want to analyze sales from a specific country? You can filter rows just like in Excel.

• Click the dropdown arrow on the 'Country' column header.
• Uncheck "(Select All)" and then tick the box next to "USA."
• Click OK.

Again, a new "Filtered Rows" step is added, capturing that exact logic.

3. Add a Conditional Column

Perhaps you want to categorize sales into "Large" and "Small" buckets based on the order amount.

• Go to the Add Column tab in the ribbon.
• Click Conditional Column.
• A simple-to-use window pops up. Fill it out like a sentence:
• Give your new column a name, like "Sale Size," and click OK.

A new "Added Conditional Column" step appears. You've just performed "if-then" logic without a single line of code.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Looking at the Code: The Advanced Editor

Curious about the M code Power BI writes behind the scenes? On the Home tab, click Advanced Editor. This opens a text window showing the complete script for your query. It might look something like this:

let
    Source = Csv.Document(File.Contents("C:\\Data\\Sales_Data.csv"),[Delimiter=",", Encoding=1252]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"OrderID", Int64.Type}, {"OrderDate", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Country] = "USA")),
    #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Sale Size", each if [Revenue] > 1000 then "Large Sale" else "Small Sale")
in
    #"Added Conditional Column"

Notice how each line references the step before it? This step-by-step logic is easy to follow and modify if you start feeling more advanced.

Part 2: Analyzing Data with DAX Queries

Once you click "Close & Apply" in Power Query, your clean data is loaded into the data model. Now, you need DAX to perform calculations on it. DAX formulas are primarily used to create two things: measures and calculated columns.

Measures vs. Calculated Columns

This is a fundamental concept in Power BI, so it's worth getting right:

• Calculated Column: Adds a new column to one of your tables. The value is calculated for each row and stored in the model, consuming memory. It's best for static labels calculated on a row-by-row basis, like combining a [FirstName] and [LastName] column.
• Measure: Doesn't store values in your table. It’s a dynamic calculation that's executed on-the-fly based on the context of your report (e.g., filters a user applies). Measures are the workhorse of Power BI and should be used for almost all aggregations (SUM, AVERAGE, COUNT, etc.).

General rule: If you can do it with a measure, use a measure. Only use a calculated column when you absolutely need to see a value for each individual row.

Writing Your First DAX Measure

Let's create a fundamental measure: Total Sales. This will give us a single number that we can later slice and dice by date, country, or product.

• Make sure you are in the Report view (the canvas icon on the far left).
• From the Home ribbon, click New Measure.
• The formula bar will appear at the top. Here is where you write your DAX.
• Type the following formula:

Total Sales = SUM(Sales[Revenue])

• Press Enter to save the measure. You'll see it appear in your Data pane on the right-hand side, usually indicated with a calculator icon.

Let's break down that simple formula:

• Total Sales is the name we're giving our measure.
• SUM() is the DAX aggregation function.
• Sales[Revenue] tells the function which table (Sales) and which column ([Revenue]) to add up.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Leveling Up with CALCULATE

The CALCULATE() function is the most important function in DAX. It modifies the "filter context" of a calculation. In simple terms, it lets you apply filters inside your formula. For example, let's create a measure that only calculates sales for a specific region.

• Create a new measure.
• Enter the following formula:

USA Sales = CALCULATE([Total Sales], 'Sales'[Country] = "USA")

This measure takes our existing [Total Sales] measure and applies a permanent filter to it, so it will only sum rows where the 'Country' column is "USA," regardless of what other filters are applied in the report. This is incredibly powerful for creating comparisons, year-over-year growth, and other key business metrics.

Final Thoughts

Mastering "queries" in Power BI means developing skills in two areas. First, using the Power Query Editor to expertly clean and prepare your data. Second, using DAX to write powerful and dynamic measures that summarize your clean data and respond to user interactions in your reports.

While diving deep into Power BI is a valuable skill, sometimes you just need to connect your marketing and sales platforms and get answers right away. We created Graphed because we believe data analysis shouldn't require learning M language or complex DAX patterns. Instead of clicking through menus to transform data, you can simply type what you want in plain English and have your entire dashboard built in seconds, letting you focus on the insights instead of the process.