How to add manually new categories inside Power Query?

[Mar82]

Dear community,

I want to improve/optimize the ETL rountine of a financial dashboard.

The old way:

• Currently data from bank accounts are exported per week as csv files
• the csv file is transformed into a excel file and the list is formated into a formated Exel Table
• the bank account data is manually copied and pasted in an excel file
• Categories to group the data are offered by drop down lists in a additional column
• The user asign the categories manually in Excel per each row
• When data is enriched by the choosen categories the whole Power Query, Data Modeling, dashboard routine starts

Dark side of that approach

• manually copy and pasting is fuzzy and error prone

Limitations:

• The bank provider doesn't offer an API to directly connect to the bank accounts
• Currently no way to change this and change of provider is not an option
• The csv export way is the way to go

My "new" approach:

• Creating a folder for each bank account
• Export csv files inculding data from the previous week by each bank account
• Store each bank accounts specific weekly csv file in the specific bank accounts folder 
• Use Power Query to import each bank accounts data 
• Append/Merge them later

Challenge:

• For some of the imported data entries which occurs regulary i can use a conditional colum using some if then formula and Power Query assigns automatically a category entry
• But some data / row entries needs a manual check up to afterwards asign a category
• How i can do this inside Power Query?

Questions:

• Is there a way to have a kind of drop down menue in Power Query available in e.g. a custom column to assign categories manually (similar to a excel column with a drop down menue)?
• What would be a smart approach to have a procedure in place to assign regulary occuring data automatically a category with the option to also assign a categories manually?

I would appricate the help of the community.

Have a nice day.

Martin

[halfglassdarkly]

Not aware of a way to assign a category to a record via a drop-down in Power Query or Power BI, but I've come up with my own solution to a similar scenario I had categorising bank statement transactions in my personal finance tracker.

I ended up creating an Excel workbook with tables to store a list of keywords that map to a specific category. I bring these tables into PowerBI and then use a calculated column in my bank statement fact table to lookup the related category:

Category = 
CALCULATE(MIN('Category Keywords'[Output Category]),
CONTAINSSTRING('Statement Fact'[Transaction Description],'Category Keywords'[Input Keyword]))

That way if a keyword in my 'Category Keywords' table exists within the transaction description in the bank statement, the category associated with that keyword is returned. Of course this only works if each keyword maps to only one category (though categories can map to multiple keywords). If you needed to return a list of possible categories based on multiple keywords you could use CONCATENATEX instead of MIN.

I wanted to be able to prioritise categorisation based on some keywords over others; to achieve this I maintain a column in my keyword table called [Specificity] which I set as "High", "Medium","Low". 

For example I might have some transactions which include the description "Indian Takeaway" while others might just include the keyword "Takeaway".

In my keyword table I include a keyword "Indian Takeaway" with [Specificity] set to "High" and another keyword "Takeaway" with [Specificity] set to "Low". That way transactions that include the string "Takeaway" get coded to my generic "Takeaway" category, unless there is a keyword match at a higher level of specificity, in this case "Indian Takeaway".

My calculated column in my statement table would then look something like this:

Category = 
Var H = CALCULATE(MIN('Category Keywords'[Output Category]),
CONTAINSSTRING('Statement Fact'[Transaction Description],
'Category Keywords'[Input Keyword]),'Category Keywords'[Specificity] = "High")

Var M = CALCULATE(MIN('Category Keywords'[Output Category]),
CONTAINSSTRING('Statement Fact'[Transaction Description],
'Category Keywords'[Input Keyword]),'Category Keywords'[Specificity] = "Medium")

Var L = CALCULATE(MIN('Category Keywords'[Output Category]),
CONTAINSSTRING('Statement Fact'[Transaction Description],
'Category Keywords'[Input Keyword]),'Category Keywords'[Specificity] = "Low")

RETURN

SWITCH(TRUE(),
H<>"",H,
M<>"",M,
L<>"",L,
"Uncategorised")

But you could also maintain other groupings to filter your keywords, e.g. specify which keywords should be used for each bank account.

This approach may not be as user friendly as what you're hoping for, but I found that it worked well for me being able to categorise large numbers of transactions. While it still requires maintaining categorisation in Excel, it's at a consolidated level rather than having to review and assign categories manually for each transaction.

Having said that, there are some cases where you may need to override the categorisation for a specific transaction, and i maintain a seperate table for this in Excel as well.

If you have access to Power Automate another option you could consider would be using this to set up a flow to send uncategorised transactions to Excel for categorisation. Theoretically you could have buttons to invoke different flows to add different categories, but that would get unweildy if you have more than a few categories to choose from.

[Mar82]

Hi @halfglassdarkly ,

Thanks a lot for your approach. I will evaluate this approach and let you know, whether It helps me to solve my problem. I'm still curious whether there is a more easy way to solve it. 

Cheers and have a nice day

Martin