How to Code in Power BI

While Power BI is famous for its user-friendly, drag-and-drop interface, relying solely on the UI is like driving a sports car in first gear. To truly unlock its power, you need to get comfortable with the languages that work behind the scenes: DAX and Power Query M. This article covers the essentials of "coding" in Power BI, guiding you through the two core languages that will take your reports from basic to brilliant.

What "Coding" Actually Means in Power BI

Let's clear one thing up: "coding" in Power BI isn't about writing Python or JavaScript to build visualizations from scratch. Instead, it refers to using specialized formula languages to manipulate your data and create advanced calculations that the standard interface can't handle.

For 99% of Power BI development, this coding happens in two places:

DAX (Data Analysis Expressions): This is a formula language used to create custom calculations. Think of it as a super-powered version of Excel formulas, but designed for relational data models. You'll use DAX to create measures and calculated columns that summarize and analyze your data.
M (Power Query M language): This is the functional language that powers the Power Query Editor. Every time you clean, shape, or transform your data using the graphical interface — like splitting columns, unpivoting data, or filtering rows — Power BI is writing M code in the background.

Mastering both is the key to becoming a Power BI expert — DAX for sophisticated analysis and M for robust data preparation.

Getting Started with DAX Formulas

DAX is where you add business logic and intelligence to your reports. It allows you to create new information from the data you’ve already loaded, enabling you to calculate metrics like year-to-date sales, profit margins, or customer growth rates.

DAX Syntax Basics

At first glance, DAX can look a lot like Excel functions. For instance, to calculate the total amount from a "Revenue" column in a "Sales" table, you’d write:

Let’s break this down:

Total Revenue: This is the name of your new measure.
= : This indicates the beginning of the formula.
SUM: This is the DAX function, in this case, a simple aggregation.
'Sales'[Revenue]: This is the reference to the data. It follows the format of 'TableName'[ColumnName]. Single quotes around the table name are only required if the name contains spaces, but it's good practice to use them anyway.

Crucial Concept: Calculated Columns vs. Measures

One of the first and most important hurdles in learning DAX is understanding the difference between a calculated column and a measure. They look similar but behave very differently.

Calculated Columns

A calculated column is a new column you add to an existing table in your data model. The formula is evaluated for every single row in that table during the data refresh, and the result is stored within your file. Because the value is pre-calculated and stored, it consumes RAM and increases file size.

When to use them: Use calculated columns when you need a static value for each row that you can use as a slicer, filter, or axis in a chart. For example, categorizing customers into tiers based on their lifetime spending.

Example: Creating a Profit Column

This calculates the profit for each row in the Sales table and physically stores that value.

Measures

Measures are calculations performed on the fly, in real-time, based on the context of your report. The context is determined by the filters applied by the user via slicers, visual interactions, or other report elements. Measures aren't stored with your data, so they don’t increase file size nearly as much.

When to use them: Use measures for almost all of your aggregation needs. Ratios, sums, averages, and complex business calculations like "Year-to-Date Sales" should all be measures. This is the bread and butter of DAX.

Example: Calculating Total Profit

This measure doesn't store any data itself. When you put it in a card visual, it calculates the sum of the profit column for your entire dataset. If you put it in a bar chart with "Category" on the axis, it calculates the profit for each specific category individually.

Golden Rule: If you're aggregating numbers, start with a measure. Only reach for a calculated column if you truly need to see a calculated value on every row or use it to slice your data.

Your First DAX Measure: Step by Step

Let's create the foundational Total Revenue measure.

With your Power BI report open, find the table you want to add the measure to in the Fields pane on the right-hand side.
Right-click the table name (e.g., 'Sales') and select New measure.
A formula bar will appear at the top. Power BI may have pre-filled some text. Delete it and type your DAX formula:

Press Enter. You'll see your new measure appear in the Fields pane, marked with a calculator icon.
To see it work, add a Card visual to your report canvas and drag your Total Revenue measure into the "Fields" area for that visual. It will now display the aggregated total revenue.

A Few Powerful DAX Functions to Learn

DAX has hundreds of functions, but you can go a long way by mastering a few key ones.

Aggregators: SUM, AVERAGE, COUNT, COUNTA, DISTINCTCOUNT, MAX, MIN. These are straightforward and work much like their Excel counterparts.
Iterator Functions (the "X" functions): SUMX, AVERAGEX, COUNTX. These are incredibly powerful. Unlike SUM([Sales]) which just adds up a column, an iterator like SUMX goes through a table row by row, performs a calculation you define, and then sums the results of that row-by-row calculation. For example, to calculate revenue without a calculated column, you could use:

CALCULATE: This is the most important function in DAX. Period. It lets you modify the filter context of a calculation. Want to calculate sales just for the year 2023, regardless of what year is selected in a slicer? CALCULATE is your function.

Uncovering Power Query M Language

If DAX is for analysis presentation, M is for data preparation. M is the language working behind the Power Query Editor that records every transformation step you apply to your data — from connecting to a data source to removing columns, splitting text, and merging tables.

Why Learn M if the UI is So Good?

The Power Query UI is fantastic, but learning the M code it generates gives you superpowers for several reasons:

Advanced Logic: You can write complex conditional logic or build transformations that are simply impossible to achieve by clicking buttons alone.
Troubleshooting: When a query breaks, you can read the M code in the Advanced Editor to understand exactly where the error occurred, rather than just clicking through UI steps hoping to find the problem.
Reusability: You can create custom functions in M to repeat a set of transformation steps across multiple queries, saving vast amounts of time.

Finding the M Code

In the Power Query Editor, you can see the M code in two primary places:

The Formula Bar: Click on any step in the "Applied Steps" pane on the right. The corresponding M code for that specific step will appear in the formula bar above your data preview. (If you don't see it, go to the "View" tab and check the "Formula Bar" box).
The Advanced Editor: This is where you see the whole show. In the "Home" tab, click "Advanced Editor." A new window will pop up showing the full M script for your entire query.

Understanding M's `let` and `in` Structure

M code follows a simple structure using let and in statements.

Let's break it down:

let: Everything between let and in is the body of the query. It's a series of steps (or variables).
Steps: Each line is a step. For example, Source = ... is the first variable, which points to our CSV file. The next step, #"Promoted Headers", references the previous step (Source) and promotes the first row to headers. Each step often builds upon the one before it.
in: This statement defines what the query should output. In most cases, it’s simply the name of the very last step.

Practical Tips for Working with M and DAX

Format Your Code: Long formulas become unreadable quickly. Use line breaks (Shift + Enter) and indentation to format a long DAX query. For M code, the Advanced Editor does a decent job, but feel free to add spacing to improve readability. Tools like daxformatter.com are great for cleaning up complex DAX.
Name Your M Steps: Don't leave the default names like "Changed Type1" that Power Query generates. Right-click and rename steps in the "Applied Steps" panel to something descriptive, like "SetColumnTypes" or "RemovedTestAccounts." This makes your queries much easier to debug later.
Use DAX Variables (VAR): When writing more complex DAX measures, use VAR ... RETURN to break down the calculation into logical pieces. Variables help organize your code and can even improve performance.

Add Comments: Just like any coding language, comments are your new best friend. In both DAX and M, an inline comment starts with //. This will save future you (and your teammates) from trying to reverse-engineer your logic.

Final Thoughts

Moving beyond drag-and-drop and into the world of DAX and M is what separates a casual Power BI user from a data professional. While leveraging the UI is great for simple reports, direct coding with these languages lets you create dynamic, insightful, and highly customized data solutions that answer complex business questions with precision.

The journey to mastering a query language, however powerful, isn’t always easy. For many non-technical teams, the time it takes to become proficient is a major bottleneck to getting answers from their data. That’s why at Graphed we’ve taken a different approach. We believe data shouldn’t require you to learn new coding languages. By connecting your SaaS tools and asking questions in plain English, you can create real-time dashboards and get instant insights, turning what once required complex DAX formulas into a simple conversation about your business.