Splitting and merging columns in Power BI - Deep Dive

Overview - Splitting and merging columns

What is it?

Splitting and merging columns are ways to change how data is organized in tables. Splitting breaks one column into two or more parts based on a rule, like separating first and last names. Merging combines two or more columns into one, like joining city and state into a full address. These help make data easier to analyze and understand.

Why it matters

Without splitting and merging, data can be messy and hard to use. For example, if names are all in one column, you can't easily sort by last name. If addresses are in separate columns, you might want to see them together. These operations let you clean and shape data so reports and dashboards show clear, useful information.

Where it fits

Before learning this, you should know how to load data into Power BI and understand basic tables. After this, you can learn about creating calculated columns, measures, and advanced data transformations to build powerful reports.

Mental Model

Core Idea

Splitting and merging columns reshape data by breaking apart or combining pieces to make information clearer and easier to use.

Think of it like...

It's like cutting a sandwich into smaller pieces to share or putting slices together to make a bigger sandwich that tastes better.

┌─────────────┐      Split      ┌───────────┐ ┌───────────┐
│ Full Name   │ ──────────────▶ │ First Name│ │ Last Name │
└─────────────┘                 └───────────┘ └───────────┘

┌───────────┐ ┌───────────┐     Merge      ┌───────────────┐
│ City      │ │ State     │ ──────────────▶ │ City, State  │
└───────────┘ └───────────┘               └───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding columns in tables

Concept: Learn what columns are and how data is stored in them.

In Power BI, data is stored in tables made of rows and columns. Each column holds one type of information, like names or dates. Think of columns as labeled containers holding similar data for every row.

Result

You can identify columns and their data types in your dataset.

Understanding columns is key because splitting and merging change how these containers hold data.

2

Foundation

Basic column operations overview

Concept: Introduce simple ways to change columns like renaming or deleting.

Before splitting or merging, you often rename columns to clear names or remove columns you don't need. These basic operations prepare your data for more complex changes.

Result

Your table has clear, relevant columns ready for transformation.

Knowing basic column operations helps avoid confusion when splitting or merging columns.

3

Intermediate

Splitting columns by delimiter

🤔Before reading on: do you think splitting by a space delimiter will always give two parts? Commit to your answer.

Concept: Learn to split a column into parts using a character like space or comma.

Power BI lets you split columns by choosing a delimiter, such as a space, comma, or dash. For example, splitting 'John Smith' by space creates 'John' and 'Smith' in two new columns. You can split by first occurrence, last occurrence, or all occurrences.

Result

One column becomes multiple columns with separated data parts.

Understanding delimiters lets you control how data breaks apart, which is essential for cleaning mixed data.

4

Intermediate

Merging columns with custom separators

🤔Before reading on: do you think merging columns always adds a space between values? Commit to your answer.

Concept: Combine two or more columns into one, choosing how to separate the joined values.

In Power BI, you can merge columns by selecting them and specifying a separator like space, comma, or none. For example, merging 'City' and 'State' with a comma and space creates 'Seattle, WA'. This helps create readable combined information.

Result

Multiple columns become one column with combined data.

Knowing how to merge with separators helps create meaningful combined fields for reports.

5

Intermediate

Using Power Query for splitting and merging

Concept: Apply splitting and merging inside Power Query Editor for better control.

Power Query Editor is where you transform data before loading it. It has tools to split columns by delimiter, number of characters, or positions. It also merges columns with custom separators. Changes here update your data model automatically.

Result

Data is reshaped cleanly before analysis, improving report quality.

Using Power Query centralizes data cleaning, making splitting and merging repeatable and manageable.

6

Advanced

Splitting columns with complex patterns

🤔Before reading on: do you think you can split a column by multiple different delimiters at once? Commit to your answer.

Concept: Learn to split columns using advanced rules like multiple delimiters or text length.

Power Query supports splitting by multiple delimiters or by number of characters. For example, splitting a product code like 'AB-1234-XY' by both dash and fixed length segments. You can also split by uppercase letters or custom patterns using M code.

Result

Complex data formats can be broken into meaningful parts automatically.

Mastering complex splits lets you handle messy real-world data formats efficiently.

7

Expert

Dynamic merging and splitting with DAX

🤔Before reading on: do you think DAX can split columns like Power Query? Commit to your answer.

Concept: Use DAX formulas to create calculated columns that split or merge data dynamically in the model.

While Power Query handles static splits and merges, DAX can create calculated columns that combine or extract parts of text dynamically. For example, using LEFT, RIGHT, MID, and CONCATENATE functions to split or merge text based on conditions. This allows dynamic updates as data changes.

Result

Your reports can show combined or split data that updates automatically with filters or slicers.

Knowing DAX for splitting and merging adds flexibility for interactive reports beyond static data shaping.

Under the Hood

Splitting and merging in Power BI happen mainly in Power Query before data loads into the model. Power Query uses a functional language called M to transform data step-by-step. Each split or merge creates a new step that changes the table structure. In DAX, calculated columns use functions to manipulate text dynamically at query time, without changing the stored data.

Why designed this way?

Power BI separates data shaping (Power Query) from data analysis (DAX) to optimize performance and flexibility. Power Query transformations run once when loading data, making them efficient for large datasets. DAX calculations run on demand, allowing dynamic changes but with more processing cost. This design balances speed and interactivity.

┌───────────────┐       Load Data       ┌───────────────┐
│ Raw Data      │ ───────────────────▶ │ Power Query   │
│ (Original)    │                      │ (M Steps)     │
└───────────────┘                      └───────────────┘
         │                                      │
         │                                      ▼
         │                             ┌───────────────┐
         │                             │ Transformed   │
         │                             │ Data Table    │
         │                             └───────────────┘
         │                                      │
         │                                      ▼
         │                             ┌───────────────┐
         │                             │ Data Model    │
         │                             │ (DAX Queries) │
         │                             └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does splitting a column by space always create exactly two new columns? Commit to yes or no.

Common Belief: Splitting by space always creates two columns: first and last name.

Reality: Splitting by space creates as many columns as there are spaces plus one. Names with middle names create more columns.

Why it matters: Assuming two columns can cause errors or confusion when data has unexpected formats.

Quick: Can merging columns in Power BI automatically remove duplicates? Commit to yes or no.

Common Belief: Merging columns automatically cleans duplicates or trims spaces.

Reality: Merging simply joins text as-is; it does not clean or remove duplicates.

Why it matters: Expecting automatic cleaning leads to dirty data in reports and wrong conclusions.

Quick: Can DAX split columns as flexibly as Power Query? Commit to yes or no.

Common Belief: DAX can split columns just like Power Query does.

Reality: DAX can only split text with simple functions and cannot handle complex splits or multiple delimiters easily.

Why it matters: Trying complex splits in DAX wastes time and causes performance issues.

Quick: Does splitting or merging columns change the original data source? Commit to yes or no.

Common Belief: Splitting or merging columns changes the original data file or database.

Reality: These operations only change data inside Power BI; the original source remains unchanged.

Why it matters: Thinking source data changes can cause confusion and fear of breaking original data.

Expert Zone

1

Splitting columns by delimiter in Power Query creates new columns but can also create nulls if data is inconsistent, which experts handle with conditional steps.

2

Merging columns with custom separators can affect sorting and filtering in reports, so experts carefully choose separators to maintain data usability.

3

Using DAX for splitting or merging is powerful for dynamic scenarios but can slow report performance if overused on large datasets.

When NOT to use

Avoid splitting or merging columns in Power Query if you need dynamic changes based on user interaction; use DAX instead. Conversely, avoid complex splits in DAX that are better handled once in Power Query. For very large datasets, consider preprocessing data outside Power BI for performance.

Production Patterns

Professionals use Power Query to clean and shape data before loading, applying splitting and merging as repeatable steps. They use DAX calculated columns for dynamic concatenations or simple splits needed in reports. They also document transformations to maintain data lineage and reproducibility.

Connections

Data Normalization

Splitting columns is a form of normalization that breaks data into atomic parts.

Understanding splitting as normalization helps grasp why databases and BI tools prefer atomic data for flexibility and accuracy.

String Manipulation in Programming

Splitting and merging columns use the same principles as string split and join functions in programming languages.

Knowing string functions in coding helps understand how BI tools transform text data efficiently.

Cooking Recipes

Splitting ingredients into parts and mixing them back resembles splitting and merging columns.

This shows how breaking down and combining parts carefully leads to better final results, just like in data shaping.

Common Pitfalls

#1 Splitting a column without checking data consistency.

Wrong approach:

Splitting 'Full Name' by space without verifying if all names have two parts.

Correct approach:

Check data for middle names or missing parts before splitting, or split by first occurrence only.

Root cause:

Assuming all data follows the same pattern leads to unexpected nulls or extra columns.

#2 Merging columns without adding separators.

Wrong approach:

Merging 'City' and 'State' columns without separator results in 'SeattleWA'.

Correct approach:

Merge with a separator like ', ' to get 'Seattle, WA'.

Root cause:

Not considering readability and data clarity when combining text.

#3 Trying to split complex patterns using only DAX functions.

Wrong approach:

Using multiple nested MID and FIND functions in DAX to split multi-delimiter text.

Correct approach:

Use Power Query for complex splits and reserve DAX for simple text extraction.

Root cause:

Misunderstanding the strengths and limits of DAX versus Power Query.

Key Takeaways

Splitting and merging columns help organize data for clearer analysis and reporting.

Power Query is the best place for most splitting and merging because it transforms data before loading.

DAX can do simple dynamic splits and merges but is not suited for complex transformations.

Always check your data patterns before splitting to avoid unexpected results.

Choosing the right separator when merging improves data readability and usability.