topic Re: Mathematical operations on Excel numbers (formatted as text) in Power Automate Desktop
https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Mathematical-operations-on-Excel-numbers-formatted-as-text/m-p/1518133#M12793
<P>My other thought is storing each cell to its own variable for each row of the loop... I think since its being stored to a DataTable, the contents of which are probably all set to the same type when read (I cant find anything to confirm or deny this on MS Docs).</P><P> </P><P>I personally give each cell read its own Variable and haven't run across any math operations issues, but I'm not sure if its considered the 'right' way to do it since it adds more steps <span class="lia-unicode-emoji" title=":face_with_tongue:">ðŸ˜›</span></P>Fri, 25 Mar 2022 16:20:18 GMTtc_procare2022-03-25T16:20:18ZMathematical operations on Excel numbers (formatted as text)
https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Mathematical-operations-on-Excel-numbers-formatted-as-text/m-p/1515786#M12743
<P>I'm iterating a DataTable of Excel values.</P><P> </P><P>I have two columns, and for each row, I want to summarize those two into a third column, but I concatenate them.</P><P> </P><P>This is the expression I use: </P><P> </P><P>% CurrentItem['Column1'] + CurrentItem['Column2'] %</P><P> </P><P>How do I get the result as a sum and not a concatenation (in the expression)?</P><P> </P><P>Example:</P><P>Column1 Column2 Column3</P><P> 2 2 </P><P> </P><P>I want the result 4 (and not 22)</P>Thu, 24 Mar 2022 14:06:08 GMThttps://powerusers.microsoft.com/t5/Power-Automate-Desktop/Mathematical-operations-on-Excel-numbers-formatted-as-text/m-p/1515786#M12743Becksie2222022-03-24T14:06:08ZRe: Mathematical operations on Excel numbers (formatted as text)
https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Mathematical-operations-on-Excel-numbers-formatted-as-text/m-p/1516589#M12760
<P>Bump to the top <span class="lia-unicode-emoji" title=":smiling_face_with_smiling_eyes:">ðŸ˜Š</span></P>Thu, 24 Mar 2022 19:39:09 GMThttps://powerusers.microsoft.com/t5/Power-Automate-Desktop/Mathematical-operations-on-Excel-numbers-formatted-as-text/m-p/1516589#M12760Becksie2222022-03-24T19:39:09ZRe: Mathematical operations on Excel numbers (formatted as text)
https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Mathematical-operations-on-Excel-numbers-formatted-as-text/m-p/1516913#M12762
<P>I think your numbers are getting stored as text, I ran some quick tests to see if I could duplicate your issue:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="tc_procare_0-1648165767015.png" style="width: 400px;"><img src="https://powerusers.microsoft.com/t5/image/serverpage/image-id/432253iEF859F5898392F25/image-size/medium/is-moderation-mode/true?v=v2&px=400" role="button" title="tc_procare_0-1648165767015.png" alt="tc_procare_0-1648165767015.png" /></span></P><P>If reading from Excel, I would check to make sure the 'Advanced' option to read as text is disabled, and if so there might be some whitespace in the original excel file to fool PAD into thinking its text.</P><P>I would then try "Covert text to number" and refer to your table value with a .trimmed property, just don't have text in your cells like I tested with my variable above <span class="lia-unicode-emoji" title=":face_with_tongue:">ðŸ˜›</span></P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="tc_procare_1-1648166044743.png" style="width: 400px;"><img src="https://powerusers.microsoft.com/t5/image/serverpage/image-id/432254iC52FE787EB366503/image-size/medium/is-moderation-mode/true?v=v2&px=400" role="button" title="tc_procare_1-1648166044743.png" alt="tc_procare_1-1648166044743.png" /></span></P><P> </P><P>Another idea to pinpoint your root cause: Set a variable to store just one of your values, and then check its type in Flow Variables once it runs (look to see if identifies as Text, or anything other than "Numeric Value", which is what you would want it to be.</P><P> </P><P>Hope this helps!</P>Fri, 25 Mar 2022 00:02:09 GMThttps://powerusers.microsoft.com/t5/Power-Automate-Desktop/Mathematical-operations-on-Excel-numbers-formatted-as-text/m-p/1516913#M12762tc_procare2022-03-25T00:02:09ZRe: Mathematical operations on Excel numbers (formatted as text)
https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Mathematical-operations-on-Excel-numbers-formatted-as-text/m-p/1517497#M12771
<P>Thanks a lot, <LI-USER uid="317425"></LI-USER> </P><P> </P><P>It happens in all flows I create, like the one below. I make an Excel sheet with three columns (A, B, and C) and here two rows. I haven't got the "Read as text" and when inspecting the value, it comes out as a text as you suggest.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Untitled.png" style="width: 999px;"><img src="https://powerusers.microsoft.com/t5/image/serverpage/image-id/432446i1B5241BBCB9242F9/image-size/large/is-moderation-mode/true?v=v2&px=999" role="button" title="Untitled.png" alt="Untitled.png" /></span></P><P> </P><P>If I use the convert text to number before the expression, it works, but I would like to know how it can be solved within an expression (to save time over and over in the future).</P><P> </P><P>This is my expression, which doesn't work: %CurrentItem['A'] + CurrentItem['B']%</P><P> </P><P>I also tried to format the excel data as numbers, and no difference. I would love not to format Excel data, as that will make my PAD flows inefficient.</P>Fri, 25 Mar 2022 10:01:22 GMThttps://powerusers.microsoft.com/t5/Power-Automate-Desktop/Mathematical-operations-on-Excel-numbers-formatted-as-text/m-p/1517497#M12771Becksie2222022-03-25T10:01:22ZRe: Mathematical operations on Excel numbers (formatted as text)
https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Mathematical-operations-on-Excel-numbers-formatted-as-text/m-p/1517503#M12773
<P>Another workaround is changing the expression to %CurrentItem['A'] * 1 + CurrentItem['B']% and then it will work, but how do I do it right? <span class="lia-unicode-emoji" title=":grinning_face:">ðŸ˜€</span></P>Fri, 25 Mar 2022 10:06:28 GMThttps://powerusers.microsoft.com/t5/Power-Automate-Desktop/Mathematical-operations-on-Excel-numbers-formatted-as-text/m-p/1517503#M12773Becksie2222022-03-25T10:06:28ZRe: Mathematical operations on Excel numbers (formatted as text)
https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Mathematical-operations-on-Excel-numbers-formatted-as-text/m-p/1518133#M12793
<P>My other thought is storing each cell to its own variable for each row of the loop... I think since its being stored to a DataTable, the contents of which are probably all set to the same type when read (I cant find anything to confirm or deny this on MS Docs).</P><P> </P><P>I personally give each cell read its own Variable and haven't run across any math operations issues, but I'm not sure if its considered the 'right' way to do it since it adds more steps <span class="lia-unicode-emoji" title=":face_with_tongue:">ðŸ˜›</span></P>Fri, 25 Mar 2022 16:20:18 GMThttps://powerusers.microsoft.com/t5/Power-Automate-Desktop/Mathematical-operations-on-Excel-numbers-formatted-as-text/m-p/1518133#M12793tc_procare2022-03-25T16:20:18Z