Split Column by Delimiter-Power Query

 


🧩 Objective: Split a single column (e.g., "Full Name") into multiple columns (e.g., "First Name" and "Last Name") using a delimiter such as space, comma, or semicolon.

✅ Step-by-Step Process in Power Query:

🔹 1. Load Your Data into Power Query

  • Open your Excel file.

  • Select your data table or range.

  • Go to the Data tab → click From Table/Range (make sure your data has headers).

🔹 2. Select the Column You Want to Split

  • In Power Query Editor, click on the column that contains combined data (e.g., "Full Name").

🔹 3. Use the Split Column Function

  • Navigate to the Transform tab (as shown in the image).

  • Click on Split Column → Select By Delimiter from the dropdown.

🔹 4. Choose Your Delimiter

  • In the dialog box:

    • Choose the delimiter (commonly a Space, Comma, or Custom).

    • Example: For "John Smith", choose Space.

    • Split at: Select Each occurrence of the delimiter or Left-most/Right-most based on need.

🔹 5. Output Columns Are Generated

  • Power Query creates two new columns: e.g., "Full Name.1" (First Name) and "Full Name.2" (Last Name).

  • You can rename these columns appropriately.

🔹 6. Apply and Load

  • Click Close & Load in the Home tab to return the data to Excel.

🛠️ Common Use Cases

  • Splitting names into first and last.

  • Separating dates and times.

  • Extracting items from CSV strings.