Split Columns Into Rows - Power Query Editor Tips and Tricks

Mitchell Pearson explores a powerful yet often overlooked feature in Power BI’s Power Query Editor: the ability to split a single column into multiple rows. This technique is especially useful when dealing with data that has been compressed into a single row but needs to be normalized for analysis.

Why This Matters

Many users are familiar with splitting columns by delimiters, but splitting into rows can be a game-changer for data transformation. Mitchell demonstrates how to handle this using both the graphical user interface (GUI) and M code, providing flexibility for users of all skill levels.

Scenario Overview

The example used involves a transaction record where multiple salespeople are listed in a single cell. The goal is to create one row per salesperson per transaction.

Method 1: Using the Power Query UI

1. Right-click the column and choose Split Column by Delimiter.
2. Use the Custom option to specify delimiters like semicolons and hidden characters such as line feeds.
3. Click Advanced Options and select Split into Rows instead of columns.
4. Click OK to apply the transformation.

Handling Hidden Characters

Mitchell emphasizes the importance of recognizing hidden characters like carriage returns and line feeds, which can disrupt data parsing. He recommends using tools like Notepad++ to visualize these characters when troubleshooting.

Potential Pitfalls

• Be cautious when splitting transactional or fact tables, as this can lead to duplicated data and inaccurate aggregations.
• Mitchell advises handling these issues either in Power Query or later in DAX calculations, depending on the dataset size and complexity.

Method 2: Using M Code

For users comfortable with scripting, Mitchell demonstrates how to achieve the same result using M code:

1. Add a Custom Column using the Text.Split function.
2. Specify the delimiters (e.g., ";" and "#(lf)" for line feed).
3. Expand the resulting list into new rows using the expand icon.
4. Remove the original column if no longer needed.

Key Takeaways

• Splitting columns into rows is a powerful feature for data normalization.
• Advanced options in Power Query can unlock hidden capabilities.
• Understanding and handling special characters is crucial for clean data transformation.
• Learning M code opens up even more possibilities beyond the UI.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power Query and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.