I am analysing my bank account data, which as downloaded comes form the bank in a fairly messy condition.
I've used Power Query to get all my monthly statements (in a single folder) and manage the basic transforming. That leaves me with perfectly clean data apart from the payee, which includes multiple similar looking entries - for example, I have so far discovered over 20 different entries which include Amazon, in one format or another.
I need to add categories to make sense of the eventual pivot table I'm going to build, and I intend to have a category and a sub-category. I'm looking at something like
Utility Gas
Utility Electricity
Entertainment Cinema
Entertainment Netflix
Misc Amazon
Misc Ebay
etc
Since the data is in such a variable format (and presumably future months will bring more variations), I believe a look-up table will be unworkable, but I hope I can manage the categorisation manually (I've only about 50 to 60 new entries per month) which should be reasonably easy to categorise after I sort the payee aphabetically to get "similar" entries close to each other.
My first trial was to add 2 extra columns to my table in Excel, manually coding the categories (not too massive a job as I only working with 1 year of data, and many entries are similar).
Being a Power Query newbie I'm nervous about this. I did a partial test entering about 50 categories, then added an extra month to my data folder to discover that the refresh imported the new data in a different order as I hadn't reset the order in the excel sheet, and so the columns I had added in Excel were now randomly allocated to incorrect imported data. Forgetting to reorder the sheet before a refresh will be an easy mistake to repeat, so I'm not happy with this option.
Can anyone suggest a better solution please. As far as I can see, doing it inside Power query does not look possible, but Finding answers is not easy in Google.
I hope this rambling note is not too long, but gives enough info to help.
Thanks
Colin
Colin
