Forum Discussion
Solved
Convert PDF to Excel with Power Query: currency columns treated as text
When I import a PDF using Power Query the Amount column (e.g. £1.75) is interpreted as Text and I can't simply re-format it to Currency. My dumb workaround is to copy column data & paste it somewher...
Take this:
- Remove the Currency Symbol
- In Power Query, select the column containing the currency values.
- Use the Replace Values function to remove the "£" symbol by replacing it with an empty string ("").
- Convert the Column to a Number Type
- After removing the currency symbol, change the column type to Decimal Number or Currency.
- You can do this by selecting the column, then clicking Transform > Data Type > Decimal Number.
- Use a Formula in Power Query (M Code)
= Number.From(Text.Remove([Amount], "£"))
I have a new question now - I'm trying to import data from a FOLDER of pdfs so that when I add a new pdf I can refresh and append the new data to my worksheet. It all works as I expect EXCEPT when I get to the step of "Combine files ¦ Select the object to be extracted from each file" I need to extract multiple objects. In the screenshot below I need to extract all the even-numbered tables but not the pages - Any ideas?
TIA
Jez
That's with modifying Transform Sample File query which is Transform File from <Query> folder. Implement logic to select proper tables, finally Table.Combine() them.
Don't think that could be done from user interface only.
