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.
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:
Without proper formatting, column names can become difficult to interpret, especially when words are combined into a single string.
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:
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.
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:
This ensures that the new transformation becomes the final step executed in the query.
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:
This method is especially useful when working with datasets that contain hundreds of columns, saving significant time and effort.
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.
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.