I have imported 2025 bank statements from a PDF using power query into Excel. However the date column in the PDF only has day/month and no year. When converting to a date it inserts the current year which at the time was 2025. When I open the same spreadsheet this year it sets the date to 2026. I can insert a line in the query to correct this, but is there a menthod that avoids having to correct the year? This is futher complicated when a statement is at the end or beginning of a year and has dates from two years.
right after you bring the file into PQ, highlight the date column and append to the column the year.
- Open the Power Query Editor.
- Select the Add Column tab on the ribbon.
- Click on Custom Column.
- In the Custom Column dialog box:
- Enter a name for the New column name (you can rename this later if you want to replace the original column).
- In the Custom column formula box, enter the formula to concatenate the values. The ampersand (
&) operator is used for string concatenation. - For example, to add the text " (Sales)" to a column named "Region", the formula would be:
= [Region] & " (Sales)" - Ensure the column you are referencing is of the Text data type beforehand, or you may need to adjust the data type afterward.
- Click OK.
- A new column will be created with the appended values. You can then delete the original column or rename the new one as needed.
Microsoft Support +4
I tried several methods, one of which entailed adding a year column based on the month then creating a new column using the day month from the original date column and adding the year.
Was not satisfied as it did not work for different pages of the PDF. Just tonight (UK) I have come up with a function that uses a lookup table that contains the all the months and replaces the values insitu.
Thanks for the reply. Hopefully people will be careful when importing bank statements.