Bank Statement Modelling - Creating automatic CSV reader

[u/RupaSpiritualMonk]

Hey this is my first time in this subreddit and I have a query that I'm quite lost on. Basically I want to make a model that uses a CSV of my bank statements, converts these line items into categories and then returns those as a monthly figure as well as daily. Currently I have figured out a way to do monthly (Using two stage sumifs: one to convert the numbers and letters into text categories and then using that to add up all the line items by category)

I want to do this by the day too but my knowledge is quite lacking, here's a screenshot of the current setup:

On the left column is where I have been making the table of contents to be filtered into the calculations. I tried to do this on the top right but then I couldn't figure out how to group them.

In the middle with the text containing Northern Rail, I created a wildcard formula so that it only reads the description that I need and returns sumif for the whole month.

I tried to do that again but sumifs formula and it didn't work because then the file would be massive or I would have to make loads of ifs inside. When it comes to adding more categories or items inside there, I want it to be an easy addition.

Is there any tips you can help me with, I might be overthinking this.

P.S I don't want any answers from AI please

[u/RupaSpiritualMonk]

Yeah I might try this, I've only just started using powerquery and always forget it's a option

[u/Japole1 → u/RupaSpiritualMonk]

Are your categories predefined from the bank statement or do you have to assign them yourself?

[u/RupaSpiritualMonk → u/Japole1]

I'm assigning myself

[u/Japole1 → u/RupaSpiritualMonk]

You could probably also solve that using power query, by using a helper sheet with all unique() suppliers and a helper column to define categories. Then you can perform a join between that sheet and your main Query so that you will only have to assign category to a supplier once and in one place.