Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Split First Name and Last Name in Power BI

Cody Schneider••8 min read

Splitting a full name into a first name and a last name is a fundamental yet sometimes frustrating task for anyone working with customer data. Power BI offers several powerful ways to handle this, moving beyond the simple text-to-columns feature you might be used to in Excel. This tutorial will walk you through a few different methods in Power BI, from a simple point-and-click solution to a more flexible formula-based approach.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Why Split Names in Power BI?

Before jumping into the "how," it's helpful to understand the "why." You might need to separate first and last names for several common reasons:

  • Personalization: Using just the first name in email campaigns or report greetings ("Hey, Sarah!") is much more personal than using "Hey, Sarah Smith."
  • Data Joining: You may need to join your customer list with another table using the first and last names as a composite key.
  • Better Analysis: You could analyze the frequency of last names to understand the origins of your customer base or search for duplicate entries more easily.
  • Data Standardization: Splitting names is often a first step in cleaning and standardizing your datasets for easier management and reporting.

Whatever your reason, the tool you'll use for most of these transformations is the Power Query Editor. This is Power BI's built-in tool for all data preparation, cleaning, and transformation.

Accessing the Power Query Editor

First things first. To perform most of these splits, you need to be inside the Power Query Editor. If you're not already there, it's simple:

From the main Power BI Desktop window, go to the Home tab and click on Transform data. This opens a new window - the Power Query Editor - where the magic happens.

Now that you're in, let's look at the best ways to split your name column.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Method 1: Split Column by Delimiter (The Classic Approach)

This is the most direct and familiar method if you've spent any time in spreadsheets. It works by identifying a separating character - a "delimiter" - and using it to split the column. For names, this is almost always a space.

Step-by-Step Instructions:

  1. In the Power Query Editor, locate and select the column containing the full names you want to split. The column will be highlighted.
  2. Navigate to the Transform tab in the ribbon at the top of the window.
  3. Find the Split Column button in the "Text Column" section. Click the dropdown arrow and select By Delimiter.
  4. A new dialog box will appear. Power Query is usually smart enough to automatically detect the space as the delimiter. If not, select Space from the dropdown.
  5. Now you need to choose how to split. You have a few options under "Split at":
  6. Click OK. Power Query will replace your original column with two new ones, likely named "Full Name.1" and "Full Name.2".
  7. To finish, simply double-click the column headers of the new columns and rename them to "First Name" and "Last Name" respectively. Press Enter to save.

Method 2: Column From Examples (The No-Code, AI Way)

The "Column From Examples" feature is one of the most user-friendly tools in Power Query. Instead of telling Power BI the rules for splitting, you simply provide a few examples of what you want, and it figures out the logic for you. It's fast, intuitive, and surprisingly powerful.

How It Works:

  1. In the Power Query Editor, select the column with the full names.
  2. Go to the Add Column tab (it's important to choose "Add Column" instead of "Transform" because we want to create new columns, not change the existing one).
  3. Click on Column From Examples in the "General" section.
  4. A new, blank column will appear on the right side of your table. In the first row of this new column, start typing the first name from the corresponding full name in the first row. For example, if the first full name is "Priya Patel," you type "Priya".
  5. Press Enter. As you start typing the next example in the second row (e.g., "Daniel" for "Daniel Lee"), Power Query's AI will recognize the pattern and automatically fill in the rest of the column with the first names. The M code it generated to perform this action will appear at the top.
  6. If the preview looks correct, click OK. Power BI will create a new column, which you can rename to "First Name."
  7. Repeat the exact same process to create the last name column. Click Column From Examples again, but this time, start typing the last names ("Patel," "Lee"). Power BI will catch on just as quickly.
GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Method 3: Using DAX Functions (For Formulas)

Sometimes you need to split names after your data has already been loaded from Power Query into the Power BI data model. This is where DAX (Data Analysis Expressions), Power BI's formula language, comes in handy. You can create new calculated columns directly in the Report or Data view.

This method offers a lot of flexibility but requires a bit of formula writing.

Creating the First Name Column

  1. Go to the Data view by clicking the table icon on the left-hand pane.
  2. Select the table that contains your full name data.
  3. In the Table tools ribbon, click New column.
  4. The formula bar will appear. Enter the following DAX formula (remember to replace 'YourTable'[Full Name] with the actual names of your table and column):
First Name = 
VAR SpacePosition = SEARCH(" ", 'YourTable'[Full Name], 1, -1)
RETURN 
IF(
    SpacePosition > 0,
    LEFT('YourTable'[Full Name], SpacePosition - 1),
    'YourTable'[Full Name]
)

Let's break that down:

  • SEARCH(" ", 'YourTable'[Full Name], 1, -1) finds the position of the first space character in the full name. We add the -1 at the end to prevent an error if no space is found.
  • IF(SpacePosition > 0, ...) checks if a space was actually found.
  • If so, LEFT('YourTable'[Full Name], SpacePosition - 1) extracts all characters from the left up to the position right before the space.
  • If no space is found, it just returns the original full name.

Creating the Last Name Column

Create another new column and use this DAX formula:

Last Name = 
VAR SpacePosition = SEARCH(" ", 'YourTable'[Full Name], 1, -1)
RETURN 
IF(
    SpacePosition > 0,
    RIGHT('YourTable'[Full Name], LEN('YourTable'[Full Name]) - SpacePosition),
    BLANK()
)

Formula breakdown:

  • LEN('YourTable'[Full Name]) calculates the total length of the full name string.
  • LEN(...) - SpacePosition subtracts the position of the space from the total length. This tells us exactly how many characters make up the last name.
  • RIGHT(...) extracts that number of characters from the right side of the full name.
  • If no space is found, it returns BLANK() instead of a name.

Handling Edge Cases and Messy Data

Real-world data is rarely perfect. So what happens when you run into middle names, suffixes, or hyphenated names? Here's how each method typically performs:

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Middle Names (e.g., "Sarah Jessica Parker")

  • Split by Delimiter (Left-most): Gives you "Sarah" and "Jessica Parker." This is often the desired behavior.
  • Column From Examples: You can train it. If you type "Parker" as the last name for "Sarah Jessica Parker," it will likely learn to grab the last word.
  • DAX: The simple formulas above extract "Jessica Parker" as the last name. More complex DAX is needed to isolate only the final word.

Suffixes (e.g., "John Smith Jr.")

  • Split by Delimiter (Left-most): Gives you "John" and "Smith Jr." again, usually what you want. You could do a follow-up split to separate the "Jr." if needed.
  • Column From Examples: If you type "Smith" as the last name, it can learn to ignore the suffix.
  • DAX: More advanced formulas would be required to identify and remove common suffixes like "Jr.", "Sr.", or "III".

Hyphenated Names (e.g., "Ava-Rose Miller")

  • All methods work fine: Because the splitting is based on the space, a hyphen in the first or last name is treated as just another character. "Ava-Rose" remains together as the first name, and "Miller" becomes the last name.

Final Thoughts

Splitting full names is a classic data preparation challenge, and Power BI provides several excellent ways to tackle it. The "Split Column by Delimiter" in Power Query is perfect for quick, straightforward jobs, "Column From Examples" offers a smart, intuitive way to handle more complex patterns without code, and DAX provides powerful, formula-based control right within your data model.

Mastering these little transformations is a big part of creating clean, effective reports. However, we know that spending hours in data prep isn't always feasible, especially when you need answers fast. At Graphed, we created a tool that skips the manual setup. It connects to popular data sources like Google Analytics, Shopify, and Salesforce and lets you build dashboards and get insights simply by asking questions in plain English, turning a half-day report build into a 30-second conversation.

Related Articles