Graphed LogoGraphed
Login

How to Merge Cells in Power BI

Cody Schneider•

If you've ever tried to right-click a few cells in a Power BI table and look for a "Merge Cells" option, you already know it doesn't exist. This can be frustrating, especially if you’re used to the freedom and flexibility of Excel. This article will show you how to achieve the visual and functional outcomes you want, not by merging cells directly, but by using Power BI's powerful data transformation tools.

We'll walk through the proper way to combine data using Power Query, explore more advanced conditional merging techniques for superior report aesthetics, and discuss alternatives like the Matrix visual that might be even better for your reporting goals.

Why Is There No "Merge Cells" Button in Power BI?

Understanding the "why" makes the "how" much easier to grasp. Power BI and Excel are built for fundamentally different purposes. Excel is a versatile, cell-based spreadsheet program perfect for static reports, one-off analyses, and flexible layouts. Merging cells is a formatting shortcut to make those static reports prettier and easier to read.

Power BI, on the other hand, is a database visualization tool. Its primary job is to create interactive reports and dashboards from structured, relational data. Every visual is connected to a column of data underneath. Merging visual cells without changing the underlying data structure would break filters, slicers, and cross-chart interactions. In the world of Power BI, data integrity comes first, and formatting follows. Therefore, to "merge cells," you need to first reshape your data in the background.

Method 1: The Standard Approach Using "Merge Columns" in Power Query

The most common reason people want to merge cells is to combine information from multiple columns into a single, more descriptive one. Think of combining a FirstName column and a LastName column into a FullName column. The right place to do this is in Power Query Editor.

Power Query is Power BI's built-in data transformation engine. It lets you clean, shape, and prepare your data before it gets loaded into your report model. Here’s the step-by-step process:

Step 1: Open the Power Query Editor

From the main Power BI report view, navigate to the Home tab on the ribbon and click on Transform data. This will launch a new window, the Power Query Editor, where you’ll see your tables listed on the left.

Step 2: Select the Columns to Merge

Choose the query (table) you want to work with from the left-hand pane. In the main data preview window, find the columns you wish to combine. To select multiple columns, hold down the Ctrl key while clicking on the column headers. The order in which you select them matters - Power Query will merge them from left to right based on your selection order.

For example, if you want "John Smith," click the FirstName column first, then Ctrl+click the LastName column.

Step 3: Find and Use the "Merge Columns" Feature

With your columns selected, you have two easy ways to find the merge option:

  • Transform Tab: Go to the Transform tab in the ribbon. In the "Text Columns" section, click Merge Columns.

  • Right-Click Menu: Simply right-click on one of the selected column headers. From the context menu that appears, choose Merge Columns.

Step 4: Configure the Separator and New Column Name

A dialog box will pop up, asking you for two things:

  • Separator: This is a small but important detail. It's the character that will go between your merged values. If you're combining a first and last name, you'll want to choose "Space" from the dropdown. You can also choose from commas, tabs, or even a custom character.

  • New column name: Give your new, merged column a descriptive name, like "Full Name" or "Product SKU". This is the name that will appear in your Power BI visuals.

After you click OK, you'll see your original columns have been replaced by the new merged column.

Step 5: Close & Apply Your Changes

The last step is to save your work. In the Power Query Editor, go to the Home tab and click the Close & Apply button. This will close the editor and load your transformed data into Power BI, where you can now use your new "Full Name" column in any table, chart, or visual.

Method 2: Creating a Merged Look with Conditional Grouping

Sometimes, merging isn't about combining data but about creating a cleaner, more readable table. You might want to display a category name (like a sales region) only once for all the repeating rows beneath it, mimicking how grouped data often looks in Excel. This improves scannability but requires a different approach.

This is an advanced technique, but it's incredibly powerful for creating highly polished table visuals. Here's a simplified overview:

1. Group Your Data

In the Power Query Editor, go to the Home tab and click Group By. Group by the column you want to serve as your "merged" category (e.g., SalesRegion). For the aggregation, create a new column named "AllData" and use the operation All Rows. This will create a single row for each sales region with a nested table containing all the original rows for that region.

2. Add an Index to Each Sub-Table

Now, go to the Add Column tab and select Custom Column. We'll use a small M formula to add a counting index to each of the nested tables you created in the step above. The formula looks like this:

= Table.AddIndexColumn([AllData], "SubIndex", 0, 1)

This creates a new column containing those same tables, but each now has a "SubIndex" column that starts at 0 and increments.

3. Expand and Apply Conditional Logic

Remove the old "AllData" column and expand the new custom column to bring all your original columns back. You will now have your data back, but with the new "SubIndex" column showing the position of each row within its group.

Finally, create one more Conditional Column with logic like this:

  • If SubIndex equals 0,

  • Then output the value from the SalesRegion column,

  • Else output null.

Once you close and apply, you can use this new conditional column in a table visual. It will show the region name for the first row and will be blank for all subsequent rows in that region, creating that clean, merged aesthetic you were looking for.

A Better Way: Using Power BI Visuals Instead of Merging

Before you go through the steps of manually merging data, it's worth asking if there's a more "Power BI-native" way to get what you want. Often, the desire to merge cells comes from trying to replicate a spreadsheet layout. Power BI offers better, more interactive alternatives.

The Matrix Visual

The Matrix visual is Power BI's answer to pivot tables and grouped reports. Instead of merging a Category column and a SubCategory column, you can drag both fields into the "Rows" well of a Matrix visual. Power BI will automatically create a hierarchical, drill-down layout. Users can expand and collapse categories with a single click, which is far more interactive and insightful than a static, merged table. This is nearly always the preferred method for viewing hierarchical data.

DAX for Concatenation

If you prefer not to use Power Query, you can also combine text fields using a DAX Calculated Column directly in the main report view. Select your table in the Data pane, click New Column, and use a simple formula like this:

FullName = [FirstName] & " " & [LastName]

This achieves the same result as Method 1. The general rule of thumb is to perform data transformations like this in Power Query when possible to keep your DAX model cleaner, but this option works perfectly well too.

Final Thoughts

Learning how to "merge cells" in Power BI is really about learning to think about your data's structure, not just its appearance. Instead of a simple formatting button, Power BI provides robust tools like Power Query to properly combine and shape your data for more effective reporting. Using an interactive visual like the Matrix can often eliminate the need to merge at all, leading to a better experience for your end-users.

We know that spending hours in tools like Power BI to transform data is one of the biggest bottlenecks in data analysis. At Graphed, we created a tool to eliminate that friction entirely. Rather than learning the multi-step process of merging columns, adding conditional logic, or configuring matrix visuals, you can simply connect your data and ask a question in plain English, like "show me our total sales grouped by region and then by salesperson's full name for last quarter." Our AI analyst handles the data transformation for you and instantly builds a live, interactive chart to answer your question, helping you get from data to decision in seconds, not hours.