When managing data in platforms like Microsoft’s Power Platform, numerical formatting can often elevate the presentation. One particular enhancement involves adding ordinal suffixes (like 1st, 2nd, 3rd, etc.) to numbers. This guide breaks down a solution on how to achieve this within the Power Platform, ensuring your data is displayed with clarity and professionalism.
Understanding the Need for Ordinal Suffixes
In certain scenarios, pure numbers may not convey the intended message. For instance, stating ‘Day 3’ doesn’t resonate as well as ‘3rd Day’. Whether you’re dealing with date elements or ranking systems, ordinal suffixes enhance readability.
Determining the Suffixes
While many may already be familiar with ordinal suffixes, let’s quickly recap for clarity:
- Numbers ending in 1 have the suffix “st” (e.g., 1st)
- Numbers ending in 2 have the suffix “nd” (e.g., 22nd)
- Numbers ending in 3 have the suffix “rd” (e.g., 43rd)
- All other numbers get the suffix “th” (e.g., 5th)
Importantly, there’s a notable exception for numbers 11 to 20. Regardless of their ending, they all get the “th” suffix.
Implementing the Ordinal Suffix Formula
Keeping the rules in mind, here’s a Power Platform formula that dynamically adds the correct suffix, considering a variable named “NumberInput”. Essentially, you would replace “NumberInput.Text” with your specific input:
With({num:
Left(Text(Value(NumberInput.Text), "00"),2)
},
If(Value(num) > 10 And Value(num) < 20,
"th",
Switch(Right(num,1),
"1", "st",
"2", "nd",
"3", "rd",
"th"
)
)
)
Applying the Formula for Date Formatting
To further contextualize the application of this formula, consider the task of appending ordinal suffixes to day numbers within dates. Here’s how to format the current date, adding the appropriate ordinal:
With({currentDate:Now()},
With({num:
Left(Text(Text(currentDate, "dd"), "00"),2)
},
Text(currentDate, "dd") &
If(Value(num) > 10 And Value(num) < 20,
"th",
Switch(Right(num,1),
"1", "st",
"2", "nd",
"3", "rd",
"th"
)
) & " " &
Text(currentDate, "mmmm yyyy")
)
)
Conclusion
Number formatting, especially with ordinal suffixes, can greatly impact data presentation and user comprehension in platforms like Power Platform. This guide provided an insight into crafting and applying such formats. If you’re navigating challenges or need further insights on the Power Platform licensing guide or other functionalities, don’t hesitate to reach out to us. Our team of experts is here to assist.
