# COALESCE Operator: Power Query Application

This tutorial will demonstrate how to use the COALESCE operator in Power Query / M language by examining two practical problems. You can watch the full video of this tutorial at the bottom of this blog.

The COALESCE operator is represented by a double question mark (??). This operator works similarly to the or operator in Python or the default function in the Power Query M Language. It returns the first operand if it exists and is not null, and otherwise returns the second operand.

The first problem deals with the conditional replacement of values. For example, if column two contains a null value, the result should return the value in column one. Otherwise, the column will retain the value from column 2.

The second scenario is a challenge provided by Excel BI in LinkedIn.

Excel BI provides daily challenges for Excel and Power Query. But in most cases, Excel challenges can also be solved with Power Query. The goal of the second example is to filter out all the strings that donâ€™t consist of two words.

## Example #1: Replace Values Using The COALESCE Operator

Open the Query Editor. Make sure that your Formula Bar and Query Setting panes are visible. If not, go to the View tab and toggle the Layout settings.

In the first example, a table containing two columns is used.

Select Column 2 and go to the Transform tab. Then, click the Replace Values option.

In the Replace Values wizard, donâ€™t make any changes and press OK.

This will generate the syntax you need. All thatâ€™s left to do is update the code in the formula bar to show the changes you want.

### Using The IF THEN ELSE Construct

In this case, the code needs to look for a null value. If Column 2 contains a null value, then it will be replaced by the value in Column 1.

Once youâ€™re done updating the code, click the check mark beside the formula bar.

This is one of the ways to conditionally replace null values in your table. However, thereâ€™s a simpler method you can use, and thatâ€™s by using the COALESCE operator in the M language.

### Using The COALESCE Operator

The COALESCE operator is represented by a double question mark (??).

You can use COALESCE when conditionally testing for null values.

To use the COALESCE operator, you first need to copy the syntax in the previous example and then click the fx tab found on the left side of the formula bar. This creates a new step in the query.

Paste the syntax and then update it using the COALESCE operator. So instead of using the If Then Else statement, place the double question mark between Column 2 and Column 1.

When Column 2 doesnâ€™t return a null, you’ll get the value from the same column. But when it does return a null, then youâ€™ll get the value from Column 1.

## Example #2: Apply Conditional Filters

In the second example, the goal is to filter out data that doesnâ€™t consist two words.

Click the filter option in your table and select a random filter. This will make Power Query generate an M code syntax.

Next, update the syntax to filter out a string that doesnâ€™t consist of two words. Since two words are usually separated by spaces, you need to use the Text.Split command. Then, you need to place it inside a List.Count function so that the result will return a list of values.

Once done, click the check mark beside the formula bar. In the resulting table, you can see that it contains strings with two words. However, thereâ€™s an Error in the last row.

An error appears because the code encountered a null value within the dataset. To prevent errors like these from happening, you need to use the COALESCE operator.

So within the Text.Split argument, place the ?? after the Name value and then specify what appears in the table if a null value is found.

Power Query Best Practices For Your Data Model
Comparing Tables In Power Query | Power BI Tutorial
DAX And Power Query | Creating Self-Sorting Columns

## Conclusion

In this tutorial, you’ve seen two practical examples where the COALESCE operator can be implemented instead of an If Then Else statement or a Try Otherwise construct.

The COALESCE operator is a useful tool for providing a default value for a variable that may be null or have another default value. It’s often used to avoid null reference exceptions or to provide a default value when working with data that may be missing or incomplete.

All the best,

Melissa de Korte

## Power Query Data Types And Connectors

This tutorial will talk about Power Query data types and connectors. You'll learn what data types are...

## Power BI Custom Calendars: Calculating For Month On Month Change – 445 Calendars

This tutorial is about how you can run time intelligence calculations over custom calendars in Power...

## DAX CALCULATETABLE Vs FILTER Function

Both CALCULATETABLE and FILTER are powerful tools for manipulating and analyzing data in DAX query, but...

## Setting Up A Dynamic StartDate And EndDate For Power Query Date Tables

I'll show you how to set up dynamic start dates and end dates using Power BIâ€™s power query date...

## Bookmarks In Power BI – Grouping by Report Page

I'm going to show you how I utilize bookmarks in Power BI. For example, if I have multiple bookmarks...

## Power Query Features: An Overview

This tutorial will discuss about the available features inside the Power Query Editor. You'll learn how...

## Creating A Master Reporting Planner For Power BI Deployment

In this tutorial, youâ€™ll learn how to create a Master Reporting Planner in the Analyst Hub to organize...

## Power BI Modulo and Integer-Divide DAX Functions

I thought itâ€™d be interesting to find a way to highlight functions and operators in Power BI that you...

## Evaluating Customer Margin Contraction Using Power BI

Here weâ€™re going to look at customer margin contraction. Weâ€™re going to try and work out what customer...

## Power Apps Basics: Development Environment And Running Your App

In this tutorial, I'll go through some Power Apps basics and walk you through how the development...

## Enhanced Data Visualization And Reporting Frameworks

Today, I'll talk about enhanced visualization frameworks for Power BI. This is the Analyst Hub, an...

## Automatically Find Your Top 3 Salespeople Per Region Using DAX In Power BI

There's so much you can do with DAX in Power BI, and one of these things is for you to be able to find...