How to convert text values to columns in Power BI?

by

“I have a column in my HR dataset containing full names (e.g., ‘Aisha Patel’). How can I split this into separate first name and last name columns in Power BI for better analysis and reporting?”

Transform strings to data columns in Power BI Query

Let’s walk through the process of converting text to columns in Power BI using a practical example.

We will assume the following Power BI data model: Table name: “Employees” with columns “EmployeeID” (Whole Number), “FullName” (Text), “Department” (Text), “Salary” (Currency).

Step-by-Step Instructions

  1. Load your data into Power BI Desktop.
  2. Go to the “Home” tab and click on “Transform Data” to open the Power Query Editor.
  1. Select the “FullName” column in your “Employees” table.
  2. Click on the “Split Column” button in the “Transform” section of the ribbon.
  3. Choose “By Delimiter” in the dropdown menu.
  4. In the dialog box that appears:
  • Select “Space” as the delimiter.
  • Choose “At the left-most delimiter” under “Split at”.
  • Set “Split into” to “2 columns”.
  • Click “OK”.
  1. Power BI will create two new columns: “FullName.1” and “FullName.2”.
  2. Rename these columns to “FirstName” and “LastName” respectively by double-clicking on the column headers.

Split strings with DAX in Power BI

If you prefer using DAX, you can create calculated columns using DAX string functions such as LEN, RIGHT, FIND etc’:

FirstName = LEFT(Employees[FullName], FIND(" ", Employees[FullName]) - 1)
LastName = RIGHT(Employees[FullName], LEN(Employees[FullName]) - FIND(" ", Employees[FullName]))

Key Concepts

  • Delimiter: The character (in this case, a space) used to separate parts of the text.
  • Left and Right functions: DAX functions that extract characters from the left or right side of a text string.
  • FIND function: Locates the position of a substring within a text string.

If something goes wrong

  1. If some names have multiple spaces (e.g., “María José García”), adjust the split to use the right-most delimiter instead.
  2. For names without spaces, create a measure to flag these for manual review.
  3. Special characters: If names contain special characters, use the “Clean” function in Power Query to remove or replace them before splitting.

By following these steps, you’ll successfully split your full names into separate columns, enabling more detailed and flexible HR analytics in Power BI.