Hardcoded Column Headers Still Causing Errors? Find Out How to Mend Your Reports in Part 2!

I’ve seen too many reports fall apart due to hardcoded column headers in Power Query. Hardcoding is like dangerously treading on thin ice. It might hold at first, but if conditions change, as column names do, the stability is compromised, plunging your queries into potential chaos! In our last edition we used a lambda function to avoid this common mistake, this week we’re taking that function up a notch. Let’s see how we can ensure our queries remain agile and error-free regardless of how the data beneath evolves.

If you missed the last edition which also deals with hardcoding of column names, please click here to read it.

🚩 The Problem:

Image A below shows our source data’s column names all in uppercase. However we require them to be in the proper case without the underscore as shown in image B.

If we had to rename the columns directly in the column headers this will hardcode each of the headers, as shown in the formula bar in image C, all the column headers, the old names and new names, have been hardcoded as shown by the text in red. Should these column names change, our query will break!

✨ The Simple Fix Using a Lambda Function and Table.TransformColumnNames

First lets get rid of the underscore. Please click here to download the practice file.

The “Source” step should be the only step in your query.

After the “Source” step click on the “Fx” in the formula bar so that a new step is added. “Source” will be returned in the formula bar as Power Query always references the previous step by default👇:

Once “Source” is returned in the formula bar, type the below code 👇:

The end result is a table with the updated column names excluding the underscore 👇:

Breaking down the code for steps 1 to 3:

Table.TransformColumnNames(Source, each Text.Replace(_, "_", " "))

1. Source is the last step that returns the table that we want to transform

2. each (Text.Replace(_, "_", " ")) is the lambda function:

   ➡️ What is a lambda function? It’s an anonymous function — it doesn't have a name and exists just to perform a specific task right at the moment you need it. Think of it as a temporary helper.

   ➡️The “each” keyword is the M Code’s way of introducing a lambda function. It tells Power Query that the following transformation, being Text.Replace in this example, should be applied to each item (in this case each column name).

   ➡️ (Text.Replace(_, "_", " ")) is the transformation that the lambda function will perform on each item it is applied to. Text.Replace takes three arguments namely:

   > The first argument is the text to be transformed. In this example we want the column headers returned by the “Source” step to be transformed. But instead of using the actual column names in the first argument, we’re going to use the underscore “(_)” to represent those column headers.

   > The second and third arguments in (Text.Replace(_, "_", " ")) are telling Power Query to replace the underscores ("_") with spaces (" ") for each column name.

3. Wrap the code from step 2 in: Table.TransformColumnNames()

   ➡️Why do we need Table.TransformColumnNames?

   ➡️Using the lambda function on it’s own will not work, so “each” and “(_)” on it’s own will not work.

   ➡️The lambda function needs to be called by a function in order for it to execute.

   ➡️Hence Table.TransformColumnNames is used to call on the lambda function to execute against each column name in the table.

   ➡️Table.TransformColumnNames takes two parameters:

   > the first parameter is the table that we want to transform, in this case it’s our “Source” table

   > and the second parameter is a function, in this case it’s the lambda function.

The final transformation:

Breaking down the code for step 5:

5. Wrap (Text.Replace(_, "_", " ")) with Text.Proper ()

   ➡️ each Text.Proper(_) is also a lambda function that is applied to each column name.

   ➡️Why is it a lambda function? Because we’re using “each” and “(_)”

   ➡️Text.Proper takes one argument which is the text to be transformed:

   > In this example the text to be transformed is the text that’s returned by (Text.Replace(_, "_", " "))

   ➡️ Text.Proper converts the first letter of each word to uppercase and all other letters to lowercase.

The end result is a table with the updated column names in proper case without the underscore. And the query has no hardcoding of column names!

📥 Your Turn: Share Your Thoughts & Topic Wishes

Grasping the concept of dynamic transformations in Power Query is your armour against the unexpected changes that data can throw at you. By preparing for change and embracing flexibility, you're not just solving a problem today; you're future-proofing your data transformations.

I would love to hear if you enjoyed today’s newsletter, please hit reply and let me know:

• 💌Loved it!

• 😐It’s okay

• 👎Do better

So, what’s next on your Power Query journey? Are there other functions or features you're curious about? Your input is what shapes 'The Query Editor', so let's hear it!

🏆 Comment of the Day:

Please click here if you would like to check out the video that prompted @dispirited8’s comment when the Missingfield.Ignore function was used. 

🌟 Recommended:

Have you noticed that the column headers in our end query, refer image 6 above, has unstructured data types? The Changed Type step hasn’t yet been performed. However when the Changed Type step is performed it will hardcode our column headers, and that’s not what we want 🤔.

If you would like to find out how to make your Changed Type step dynamic without hardcoding your column headers, please watch this video here:

If you would like to support the newsletter, please click here.

Until next time, keep those queries rolling, and may your data always be clean and your transformations swift!

Happy Querying!

Thanks for reading.

Miss Microsoft