Logic app excel decimal number returning incorrectly - 6E-05

Gareth Terblanche 311 Reputation points
2022-03-24T15:26:44.357+00:00

I have a logic app that lists rows in a table which contains exchange rates. The numbers have a max of 5 places after the decimal. The issue that I have is that when the number has 4 zeros after the decimal and then a number this does not return a number to the logic app.

EG if the number is 0.00006 the returned value is 6E-05 where 6 always equals the last number i.e. if the number was 0.00007 the retuned value is 7E-05. The obvious work around to this is to run it through a formula which fixes the number but I feel that this is a work around rather than a proper solution. I've tried formatting the number in the excel table as text, general and even with more decimals but nothing seems to work.

Any ideas?

Azure Logic Apps
Azure Logic Apps
An Azure service that automates the access and use of data across clouds without writing code.
2,996 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,689 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. MayankBargali-MSFT 70,016 Reputation points
    2022-03-25T05:12:16.893+00:00

    @Gareth Terblanche Thanks for reaching out. You need to format the cell as text as mentioned in the excel end. I have tested the same at my end and as excel treats it as text the logic app returns it correctly.

    Treats the content of a cell as text and displays the content exactly as you type it, even when you type numbers.

    186788-image.png


  2. Gareth Terblanche 311 Reputation points
    2022-03-30T06:38:04.987+00:00

    I have decided that I'll just work with my workaround. I.e. using a formula fix the number.
    The formula is as follows:
    if(contains(items('For_each')?['AUD'],'E-05'),concat('0.0000',substring(items('For_each')?['AUD'],0,1)),items('For_each')?['AUD'])

    0 comments No comments