Power Query: Add Column Header Spaces

In this tutorial, Allison Gonzalez, a trainer at Pragmatic Works, walks through an efficient way to clean up column headers in Power Query. Data often comes in messy formats, especially when column names are compressed together without spaces. Instead of manually renaming each column, Allison demonstrates how to automate the process using M code in Power Query.

Why Cleaning Column Headers Matters

Allison emphasizes that data cleaning is a critical step in any Power BI workflow. During her Power BI boot camps, one of the first tasks she teaches is organizing and standardizing column names. Clean column headers improve:

• Readability for developers and analysts
• Usability for report consumers
• Consistency across datasets
• Efficiency when building reports and dashboards

Without proper formatting, column names can become difficult to interpret, especially when words are combined into a single string.

Understanding Power Query M Language

Before diving into the solution, Allison explains the structure of M code, also known as the Power Query formula language. Every Power Query script consists of two main sections:

1. Let Expression – Contains all transformation steps
2. In Expression – Outputs the final result

Each step in the let section corresponds to an applied step in the Power Query interface. These steps are executed sequentially, where each step references the previous one. This chaining effect ensures that transformations are applied in order.

The in statement always points to the final step in the process, triggering the execution of the entire query.

Automating Column Header Cleanup

Instead of manually editing each column name, Allison introduces a faster method using a custom M code snippet. This approach transforms column headers by inserting spaces before capital letters, making them more readable.

The process involves:

1. Opening Power Query via the Transform Data option in Power BI
2. Selecting a query (table) to modify
3. Accessing the Advanced Editor
4. Adding a new transformation step using M code

Steps to Apply the M Code

• Locate the last step in the M code (commonly “Changed Type”)
• Add a comma at the end of that step
• Paste the new transformation code on the next line
• Update the in statement to reference the new step (e.g., “Add Spaces”)

This ensures that the new transformation becomes the final step executed in the query.

How the Transformation Works

The inserted M code uses a function that scans column names and splits text based on capital letters. It then inserts spaces between words, effectively converting:

• CustomerName → Customer Name
• OrderDate → Order Date
• TotalSalesAmount → Total Sales Amount

This method is especially useful when working with datasets that contain hundreds of columns, saving significant time and effort.

Applying the Solution Across Multiple Tables

Allison demonstrates applying this technique to multiple tables within the Power Query editor, including customer and date tables. The same steps can be repeated for any dataset requiring cleanup.

After applying the transformation, users can select Close & Apply to load the cleaned data back into Power BI, where the updated column names are immediately reflected.

Key Benefits of This Approach

• Eliminates repetitive manual renaming
• Improves efficiency for large datasets
• Enhances data readability and usability
• Leverages reusable and customizable M code

Final Thoughts

Allison Gonzalez highlights that Power Query offers multiple ways to achieve the same result, but using M code provides a scalable and efficient solution. By understanding the structure of M and leveraging the Advanced Editor, users can significantly streamline their data preparation process.

This technique is a valuable addition to any Power BI developer’s toolkit, especially when dealing with messy or unstructured data sources.

 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.