How to trim tail zero under Power Automate flow

Photo from: https://safe.menlosecurity.com/https://techsbcn.com/en/how-to-get-the-best-performance-with-power-automate/

Image that if we have one application continues to log down user’s login information (format) as below under the share point (network drive):

2022-05-06 06:31:51.666,Ben,Login successfully.
2022-05-06 06:35:10.16,Andy,Account locked. (Password inconsistent)
2022-05-06 06:37:22.125,Vivian,Login successfully.
2022-05-06 08:11:26.331,David,Account locked. (User/Password not match)
2022-05-06 10:06:26.125,Elizabeth,Login successfully.
2022-05-06 18:36:11.1,Joe,Login failed. (User not existed)
2022-05-06 20:16:33,Art,Login failed. (Two factor verified failed)

Now we got one mission that we having timestamp as key, we would like to go through the login record over above file and write the relative information back to database for further business analysis purpose.

Cause the file was over the net drive share point, the Power Automate would be suitable to handle the case for us🙃. The tricky point here was that we found that timestamp inside the file was format: “yyyy-MM-dd HH:mm:ss.fff” but trim the zero of the tail (2022–05–06 06:35:10.160 -> 2022–05–06 06:35:10.16).

So we need to pre-processing the key of timestamp while passing into the flow of Power Automate. Below was the lab how we verify the action we designed for the pre-processing:

Flow of Power Automate

1. Create the manual trigger flow.
2. Input the key of timestamp string format in: “yyyy-MM-dd HH:mm:ss.fff”.
3. Initial variable (called ‘data’) and assign input on it.
4. Create Condition flow to check input with value or not.
5. Convert the variable: data to trim the tail zero.

The Convert function would be combined several built-in functions as below:

formatDateTime(variables('data'), 'yyyy-MM-dd HH:mm:ss.FFF')
or
if(endsWith(variables('data'), '0'), substring(variables('data'), 0, sub(length(variables('data')), if(less(sub(3, nthIndexOf(slice(variables('data'), -3), '0', 1)), 3),  sub(3, nthIndexOf(slice(variables('data'), -3), '0', 1)), 4))), variables('data'))

Please note that the convert function inside the (step-5) accept to be placed in single line only.

The function will handle below case(s) of trim tail zero:

1) yyyy-MM-dd HH:mm:ss.ff0 -> yyyy-MM-dd HH:mm:ss.ff
2) yyyy-MM-dd HH:mm:ss.f00 -> yyyy-MM-dd HH:mm:ss.f
3) yyyy-MM-dd HH:mm:ss.000 -> yyyy-MM-dd HH:mm:ss
4) yyyy-MM-dd HH:mm:ss.0f0 -> yyyy-MM-dd HH:mm:ss.0f
5) yyyy-MM-dd HH:mm:ss.fff -> yyyy-MM-dd HH:mm:ss.fff

Reference

• https://powerusers.microsoft.com/t5/General-Power-Automate/Remove-Leading-Zeros/td-p/814385
• https://stackoverflow.com/questions/54477985/how-can-i-pad-a-string-with-a-variable-number-of-leading-zeros