Issue with number stored as text when extracting data from pdf to excel

[Bfarrell95]

Hi All 

 

I am having an issue with my Power Automate Flow when I am Extracting data from a pdf document to an Excel sheet.  I am trying to pull in some figures from the PDF into my excel table and then analyse the Max, Min and Count of the figures.  I have attached a screenshot of this part of the table.

The issue that I am having is that when these figures are added into my table, they are being stored as Text and the formulae do not work until I click into the cells individually.  Is there a way to fix this as I am hoping for this to be fully automated and do not want to have to click into each cell every time new data is included in the table.

I have also attached a copy of the sample area which I have been pulling the information from in the PDF.  In my document automation model the figures are being extracted by their field as I have been unable to get it working as a table as I cannot figure out the code.

  

Thanks in advance.

[wskinnermctc]

You will need to get the currency from the PDF to be formatted as a number within the flow as currency or integer and then put into the excel sheet. I'm guessing the euro/dollar sign will probably be the hard part to skip over or remove from the text.

You should try to replace/remove the euro/dollar sign with a replace() expression. Then convert the text to a float integer.

I don't have the steps exactly how to do it, but you can test and use this as an example.

https://debajmecrm.com/how-to-convert-a-string-to-currency-format-in-power-automate/ 

[Bfarrell95]

Thanks for that @wskinnermctc however, unfortunately I am not able to get that working for me in the way that I want it to.

[spmofulton]

@Bfarrell95 so what were you not able to get working?

I suspect this might be a post-extract issue (where you need power automate to extract that data).  Then you need a different power automate to transform the data.   Since a good way to fix this might be power query, I have asked if its possible to automate power query using power automate.
https://powerusers.microsoft.com/t5/General-Power-Automate/Can-I-use-power-automate-on-power-query/m...

[Bfarrell95]

Thanks @spmofulton @wskinnermctc 

I have actually managed to fix this by changing the layout on the PDF so that the currency sign is further away from the first number and the automation tool is not picking up the currency sign.  

Brian

[spmofulton]

Glad you were able to get things fixed.   I was able to get a reply that is possible to automate power query(*), so if you get stuck again, then please don't hesitate to reach out.

(*) P.S. Because then could you solve this by creating a quick power automate script following wskinnermctc instructions.