Forum Discussion
Data categorization and labeling based on a criteria list
Hi!
I have a very large list of transactions and I am attempting to use powerbi to categorize these transactions based on a narrative field in the transaction list, containing certain text. I have seen the following solution which largely achieves what I'd like to do based on a conditional column and seems great for a small number of criteria you'd like to test.
However, my problem is that I have about 100 criteria flags that can be used to categorize transactions. Ideally, I'd like to have a category table with a flag field and a category field - that the transaction narrative can be tested against. Like so:
Transactions table I'd like to categorize by text found in the narrative field:
| Transaction # | Narrative |
| 1 | Shop 123 |
| 2 | Shop 241 |
| 3 | Online 122 |
| 4 | Chargeback |
| 5 | Tax |
| 6 | Refund 123 |
Criteria Table - this has about 100 criteria to test against:
| Narrative Contains | Category |
| Shop | In store |
| Online | Online |
| Chargeback | CC |
| Tax | Tax |
| Refund | In store |
Ideally I'd be able to create a table like so;
| Transaction # | Narrative | Category |
| 1 | Shop 123 | In store |
| 2 | Shop 241 | In store |
| 3 | Online 122 | Online |
| 4 | Chargeback | CC |
| 5 | Tax | Tax |
| 6 | Refund 123 | In store |
Any ideas? I'm totally lost. In excel for each row in the transaction table, I'd iterate over the category flag list - either through an array formula or a for loop in VBA - and return the corresponding value from the category column if a match is found (first match is fine). But I'm new to BI so I'm a tad lost....
Thanks!
Ok I have solved this. Here's how:
I created a new column in my transaction table with the following formula:
MatchFound= FIRSTNONBLANK( FILTER( values('Keyword Table'[Narrative Contains]), search('Keyword Table'[Narrative Contains],'Transaction list'[Narrative],1,0) ) ,1)This formula returns the matching value found within the narrative string. I can then use this with a lookup formula, to return the corresponding category:
category = LOOKUPVALUE( 'Keyword Table'[Category],'Keyword Table'[Narrative Contains],'Transaction list'[MatchFound] )
And voila! My transaction list is now categorized by searching the a narrative field to see if it contains any item from a list of keywords paired with categories...
7 Replies
- Vvelarde
Community Champion
hi, i know that exist much better solutions but lets try:
1. Insert in both tables a Custom Column with the Value of 1.
2. Merge both Tables in a New Query
3. You obtain a table like this:
4. Expand the Table
5. Insert a New Custom Column:
6. Filter this Column to only 1
7. Delete the other Columns
8. Ready
Regards
Victor
Lima - Peru
Think this is a good solution for smaller datasets. Thank you so much for taking the time to post Vvelarde
However with my dataset I have about 2m rows in my initial transaction listing - and about 100 criteria - so this would result in a 200m row table. I'm a little afraid to try this as I feel this surely would overload things...
Any other ideas for large datasets?
