Hi
Hopefully someone can help with a problem that I cannot solve.
My objective is to get a .CSV file of all bank transactions for a month, which has been saved in a folder containing all transaction files for the various bank accounts for the year i.e. Barclays Main account for March 2022 would be saved as Bank Statement/2022/BarclayMain3.CSV
To do this I have created a EXCEL spreadsheet (BanksMonths see below) from which I can via Macos in a Excel spreadsheet select YEAR – BANK – MONTH
| BANKS | Year | Status | MonthNo | Available |
| BarclayMain | 2022 | N | 1 | N |
| BarclayMain | 2022 | N | 2 | N |
| BarclayMain | 2022 | N | 3 | Y |
| BarclayMain | 2022 | N | 4 | N |
| BarclayMain | 2022 | N | 5 | N |
| BarclayMain | 2022 | N | 6 | N |
| BarclayMain | 2022 | N | 7 | N |
Once selected the Status for that Bank-MonthNo would be changed to Y. I have created a Query that would filter down to:
| BANKS | Year | Status | MonthNo | Available |
| BarclayMain | 2022 | Y | 3 | Y |
From this Query I want to create another Query which gets the Transaction file of the Bank-MonthNo from the YEAR Folder. I can then transform the transactions into useful data. i.e.
The source for the Query would be based on selected Year – Bank – MonthNo i.e.
= Csv.Document(File.Contents("C:Bal_ShtBank Statements” & Year & “” & Bank Name & MonthNo & “.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None])
How do I do this?
The other small question is there a way of checking that a file exists in EXCEL / Power Query so that I can automatically update the BanksMonths list?
Regards
Keith Weall
Hi Keith,
Please see lessons 7.03 and 7.04 of the Power Query course on dynamic file and folder paths. You can modify these techniques for your needs.
Mynda
Hi Mynda
I am not sure I understand how it works, but it works. Thankyou very much.
Keith
Great to hear, Keith. The more you become familiar with Power Query, the more it will all make sense
