Can you Escape the Maze of Nested IF Statements?

Unlock the Path to a Simpler Code

Author

Miss Microsoft
December 20, 2023

Last week we explored how just two keystrokes, “??” can effectively handle missing data and ensure that your query can run without errors.

This week our focus is on a common challenge: the labyrinth of nested IF statements, especially when dealing with multiple data points. Today I will show you how the Coalesce function, represented by '??', simplifies this process and turns a complex maze of conditional checks into a straightforward, single-line command.

💡Today's Goal: Significantly simplify your M code logic and improve your code readability using the “??” operator.

🚩 The Problem:

You have a series of quarterly sales values for individual products and you need to provide the last sales value for each of these products. The format of the source data is shown below:

The desired end result:

🔧 The Complex Fix:

This would be the traditional approach, a series of nested IF statements:

✨ The Simple Fix Using "??"

This is the simple one line of code using coalesce:

Breaking Down the Code:

  • The code begins by checking the value first in [Q4] for a non-null value

  • Where null is found it progresses to the rest of the columns [Q3], [Q2], and [Q1] in that sequence until it finds a value that isn't null and returns that value

  • If all values are null, it defaults to the value in [Q1] regardless of whether it is null or not.

📥Your Turn: Share Your Thoughts & Topic Wishes

In essence, this code is a more simpler version of a nested IF statement for selecting the first available non-null value from a series of columns. It significantly simplifies the logic and improves readability.

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

Also if there’s anything you would like me to cover please let me know.

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

Happy Querying 😊.

Thanks for reading!

Reply

or to participate.