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