Forum Discussion
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 somewhere, find & replace "£" (with nothing) and cut & paste back where it came from.
I guess there's a better solution?
Thanks
Jez
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], "£"))
6 Replies
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.
Many thanks!
If Power Query regional settings for the current file are set on English (United Kingdom), Change Type on Currency or Decimal Number works. Otherwise as Kidd_Ip suggested.
One more option here could be Transform->Extract->Text After Delimiter, take £ as delimiter, next change data type for the column.
Sounds good - I’ll try it later. Many thanks!
jez
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], "£"))
