Is Hardcoding Breaking Your Reports? Learn How to Fix It Now!

Happy Valentine’s Day Data Wizards! 💖

Today I want to share my love and affection for Power Query with you through a special three-part series. Think of Power Query as that special someone who never lets you down, especially when you treat it well by avoiding the pitfalls of hardcoding. Just like in a relationship, the secrets are flexibility and understanding😉 .

🚩 The Problem:

Image A below shows our source data’s column names. However we require the prefix “FY’24” to be added to each column name as shown in image B.

But 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 below. 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

We’re going to use a smarter, more dynamic approach using a lambda function and Table.TransformColumnNames.

Please click here to download the practice file. (The practice file has a bonus example where I give you the code for adding a suffix to the column headers).

First ensure that the “Source” step is 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 new column names that has the prefix added and there is no hardcoding of column names! 👇

Breaking down the code:

Table.TransformColumnNames(Source, each "FY'24 " & _)

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

2. each "FY'24 " & _

   ➡️each "FY'24 " & _ 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 operation following it should be applied to each item in the current context (in this case each column name).

   ➡️"FY'24 " & _ this part is the operation that the lambda function will do to each item it is applied to. It concatenates the string "FY'24 " with each item.

   ➡️The underscore “(_)” is a placeholder, it represents the current item being processed. In this example it is representing each column name specified by “Source”.

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

   ➡️Why do we need Table.TransformColumnNames?

   ➡️Using the lambda function 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.

4. The end result is a table with the updated column names with the prefix added and no hardcoded columns.

📥 Your Turn: Share Your Thoughts & Topic Wishes

Hardcoding might seem like a quick fix – but this 'fix' can backfire spectacularly. When your data source evolves, and column names change, hardcoded transformations can cause your queries to fail miserably. Employing a smarter, more dynamic approach by using a lambda function and Table.TransformColumnNames creates a resilient system, making your queries more nimble and adaptable to change.

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

Are there other functions or features you're curious about? Your input is what shapes 'The Query Editor', so please let me know!

🏆 Comment of the Day:

Please click here if you would like to check out the video that prompted KalebBroadhead’s comment.

🌟 Recommended:

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

👀 Sneak Peek for the Next Edition:

In our next edition we’re taking it up a notch. We will see how we can ensure our queries remain agile and error free regardless of how the data beneath evolves. Stay tuned!

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

Happy Querying!

Thanks for reading. Until next time!

Miss Microsoft