This lesson's interactive features are locked, please to keep using them

Split a Column

Sometimes data isn't neatly organized; for example, an address might be stored in one long string like:

123 Main Street, Springfield, OH 20324

In Power Query we can quickly split a column into separate fields using a delimiter, like a comma, dash, or pipe. This helps you clean up messy data and make it easier to use.

You'll see this a lot in real data. Combining multiple values in one cell is (unfortunately) super common, but luckily easy to fix.

In our website_sessions dataset, we have a browser_os column that combines the browser and operating system with a pipe delimiter (like Chrome|Windows). Let's split it into separate columns.

Assignment

Open Power Query on your website_sessions dataset to edit its query.
Open the Transform tab at the top next to "Home".
Highlight the browser_os column.
Click Split Column:
- "By Delimiter"
- Custom delimiter → use the pipe character |
- Click "OK" to apply the step.
You'll now see the browser_os data split into multiple columns:
- browser_os.1 = browser
- browser_os.2 = operating system
Right-click on each column heading and rename them to browser and os – notice that those also count as steps in our "Applied Steps"!
Return to the Home tab of the Power Query editor and Close & Apply the query.

Save the project and, from the course directory, submit the CLI tests.