<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=612681139262614&amp;ev=PageView&amp;noscript=1">
Skip to content

Need help? Talk to an expert: phone(904) 638-5743

Four Underutilized Power FX Formulas

Four Underutilized Power FX Formulas


In this article, Nate Halliwell is going to delve into some powerful yet often overlooked PowerFX formulas that can drastically improve your Canvas app development. Whether you're a seasoned app maker or just starting out, these formulas are must-haves in your toolkit. We'll explore four underutilized formulas: IFERROR, SUBSTITUTE, COALESCE, and SWITCH. 

IFERROR: Enhancing User Experience

As app makers, we want to ensure a smooth and error-free experience for our users. One common issue arises when displaying users' images, which sometimes leads to errors when searching for a person. To overcome this, we can utilize the IFERROR formula. By encapsulating the code that retrieves the user's image with IFERROR, we can gracefully handle the error and display a placeholder image instead. Users will no longer be presented with confusing error messages, improving their overall experience.

SUBSTITUTE: Cleaning Up Text Fields

Text fields in tables often contain unnecessary or repetitive information that clutters the user interface. The SUBSTITUTE formula comes to the rescue! Let's say we have a department field that includes the word "trainer" before the actual department name. To remove this redundant information, we can use SUBSTITUTE. By substituting the "trainer-" prefix with a blank value, we effectively remove it from the displayed text. This simple formula helps clean up our app interface and presents cleaner data to users.

COALESCE: Simplifying Conditional Statements

We've all been there – writing repetitive IF statements to handle blank values. The COALESCE formula offers an elegant solution to this problem. Instead of constructing multiple IF statements, COALESCE allows us to specify a list of values and returns the first non-blank value. Let's say we want to display the department of each user, but some users don't have a department listed. With COALESCE, we can provide a default value ("Unknown" in this case) when a blank value is encountered. This streamlines our code and improves maintainability.

SWITCH: Simplifying Nested IF Statements

Nested IF statements can quickly become unwieldy and hard to read. Enter the SWITCH statement, which offers a more concise and readable alternative. Instead of chaining multiple IF statements to compare a single value against different criteria, we can use SWITCH to provide all the match values and their corresponding results in a structured manner. For example, we can define different colors based on the department of an employee using SWITCH. This approach allows for easier maintenance and enhances code readability.


You have uncovered some powerful yet often underestimated PowerFX formulas. By incorporating IFERROR, SUBSTITUTE, COALESCE, and SWITCH into your Canvas app development workflow, you can significantly enhance the user experience, streamline your code, and unlock the full potential of your applications. Remember to experiment with these formulas and adapt them to your specific use cases to maximize their benefits. 

 Stay tuned to the Pragmatic Works' YouTube channel for more tips and tricks to elevate your app development skills. And if you're interested in learning more about Microsoft applications including Power BI, Power Automate, Excel, etc., then sign up for the Pragmatic Works' on-demand learning platform

Sign-up now and get instant access

Leave a comment

Free Trial

On-demand learning

Most Recent

private training

Hackathons, enterprise training, virtual monitoring