cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
crf200
Advocate III
Advocate III

Using Integers from an Excel Get Rows

There have been some updates to the Excel Get Rows actions. I used a function to calculate the inverse of a column and update a sharepoint list with the new (now positive) value. 

 

mul(items(*Action*)?['Amount'],-1)

 

In the last month since I used that formula some updates were made that changed the way Flow recognizes this column and now pulls as a string instead of an integer.

 

Is there an fix for this while MS tries figure out how to recognize a column thats fomatted as a number (currency, amount, ect)?

7 REPLIES 7
v-yuazh-msft
Community Support
Community Support

Hi @crf200,

 

Do you want the Amount column in excel table to be seen as number type?

 

I have made a test on my side, if you want the Amount column in excel table to be seen as number type, you could add a int() expression beside the expression of the Amount column, and the expression on your side should be :

mul(int(items(*Action*)?['Amount']),-1)

 

Best regards,

Alice

Hi Alice,

 

Int Capture.JPG

 

 

 

 

 

Its not recognizing the string as a valid parameter.

 

I've even separated the int() into a separate compose with the same outcome.

 

-Chad

 

Hi @crf200,

 

Please don't rename the Apply to each on your side, I afraid that the expression in invalid for you have rename the Appply to each with wrong formate name.

 

I have made a test on my side, the screenshot of the flow as below:

Capture.PNG

 

The expression in the Condition as below:

mul(int(items('Apply_to_each')?['Amount']),-1)

The flow would run successfully as below:

Capture.PNG

 

 

Best regards,

Alice

 

 

I completely redesigned the flow as it worked for some list items but would fail on others when it was built just like that. I saw the new 'List Items from Table' that connects with Sharepoint Online so adopted it on the rebuild so other members of my team can also have access to the files.Capture 1.PNG

 

 

 

 

 

The new action uses Excel Online but even opening the online file and ensuring the columns were formatted appropriately the flow still failed.Capture.PNG

 

 

 

crf200
Advocate III
Advocate III

I've been running some recent tests and I've found a formula that works on only some entries but fails on others. 

 

mul(int(items('ATE2')?['Amount']), int(-1))
 

Any other ideas?

 

Failed.PNGSuccess.PNG

artFRC
Frequent Visitor

I'm having a very similar experience trying to calculate the total of a set of numbers. They are stored in an Excel online sheet on Sharepoint (used in MS Teams).

 

On the excel online step 'List rows present in an table' I am using an OData filter query for today's date in the format yyyy-MM-dd:

Date eq '@{add(div(sub(ticks(utcNow('yyyy-MM-dd')),ticks('1900/01/01')),864000000000),2)}'

 

Then into an Apply to each with an Increment variable with a similar formula to the above:

int(items('Apply_to_each')['Total_Weight_(kg)'])
 
The column is formatted as 'number' in Excel online, but the flow only considers the numbers '0.0' and '31.0' as valid. This is a calculated column - but I can't see how that would lead to an intermittent fault like this, and as part of troubleshooting I created a new column 'Weight2' with only values, formatted as numbers, causing the exact same behaviour as the formula version.

 

Any tips?

artFRC
Frequent Visitor

Seems obvious now - it is because some numbers are integers but most are decimals!

 

Only 0 and 31 are integers in this set - the others are numbers to one decimal place.

 

The solution is to use float instead... so the outline of this successful flow was:

 

  • Trigger
  • Initialize variable: type = Float
  • List rows present in a table
  • Apply to each: output = value from the above step
    • Increment variable: expression: float(items('Apply_to_each')?['Total_Weight_(kg)'])
  • Send push notification


These threads didn't address my problem but may be of interest for some who have stumbled onto this thread with related issues:

https://powerusers.microsoft.com/t5/Building-Flows/Type-Conversion-Errors-decimal-to-int/td-p/289216 

https://powerusers.microsoft.com/t5/Building-Flows/decimal-place/m-p/50986#M4710 

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Users online (65,561)