This Expressions Cookbook contains some of the expressions that I find useful. I’ll add more over time, so don’t be discouraged if you only see a few.
Convert a relative to an absolute URL
If you have a reference in your HTML that is a relative URL, like, “/area/microsoft/powerautomate/” you need to be converted to absolute, you can use the expression bellow to get “https://manueltgomes.com/area/microsoft/powerautomate/”
replace(<string with the relative url>,'href=''/','https://manueltgomes.com')
After this, you can use the new URL in your “href”.
Format Date & Time
ISO 8601 (year, month and day)
Here’s how to display a date in the ISO 8601 format. This format is usually used in Europe, and it displays the date as “year-month-day hour:minute:second”. Please note that the hour is in 24h format, and all of the values are always represented with 2 digits. In the following example, please replace your variable with the “utcNow()”.
Based on the user’s locale
If you want the date to show up always in the user’s locale, you can define it with the “d” or “D” format. Both Power Automate and Power Apps will display it the same way. In the following example, please replace your variable with the “utcNow()”.
Saturday, March 13, 2021 (en-US)
Sábado, 13 de Março de 2021 (pt-PT)
Samstag, 15. März 2021 (de-DE)
Convert an Excel Number into a Date
Excel displays the dates as we would expect but store them in a number format. This number is the elapsed days since the 1st of January 1900. To convert into a date, you have first to create that date and then add the days to that date—one note. You need to remove one day; otherwise, you’ll get the next day of the date. This is because the reference date is the value “1” and not “0” so adding those days will result in one extra day that we need to remove.
addDays(formatDateTime('1-1-1900','dd-MM-yyyy'),sub(<variable with the excel number>,1))
How to use the expressions
There’s no big trick in how to use them. You can copy and paste them into your Flows. If you need help or something is not working as expected, let me know. You can also check our articles on Microsoft Power Automate to learn more about the tool and know-how to create your own. I encourage you to download the templates and adapt them to your needs. Don’t worry about attribution. It’s not necessary, but I appreciate it if you do.
Have a cool Expression?
Awesome, send it my way via email with a simple explanation of what it does so that I can include it here in this cookbook. I’ll attribute submitted templates, so please include any information you want to be displayed. I’ll test them first to ensure quality and that they don’t do anything strange. I’ll only post them in this expressions cookbook if I find something worthwhile that others can benefit from. It can also take me a while, depending on the workload that I have at the time, but I promise to look at all of them. Please also submit only expressions that you created, and you have permission to share. I cannot be considered responsible for any provided expression being proprietary. Please contact me if you find any violation of the terms described below, and I’ll remove it immediately.
This cookbook’s expressions have been tested as of the date each was added to manueltgomes.com for compatibility with the then-current versions of Microsoft’s Power Automate. Please feel free to use these expressions and adapt them to fit your specific needs. However, please keep in mind that Manuel T. Gomes and his team can not guarantee that this cookbook’s expressions will remain compatible with future updates. Moreover, the expressions included in this expressions cookbook are provided free of charge and as-is without any express or implied warranties, including implied warranties of merchantability, fitness for a particular purpose, and non-infringement. The Manuel T. Gomes Team makes no specific promises about the expressions, their particular functions, or their reliability, availability, or ability to meet your needs. Also, the Manuel T. Gomes Team will not be liable to you for any lost profits or other consequential, special, indirect, or incidental damages arising out of or connected with your use of any of the expressions in this cookbook.
Photo by Micheile Henderson on Unsplash