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], "£"))
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.