Data categorization and labeling based on a criteria list

[robbieinoz]

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.

http://community.powerbi.com/t5/Desktop/Categorize-column-if-it-contains-part-of-text/m-p/134596#M57...

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!

[Vvelarde]

@robbieinoz

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

[robbieinoz]

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...

[robbieinoz]

Any other ideas for large datasets?

[robbieinoz]

I found this solution for at least idenitfiying if there is a keyword match, but I still can't figure out how to return the corresponding category. This function can be thought of like a "containsx" formula.

In my transaction table I added a column with the following formula:

KeywordMatch = if(
SUMX('Keyword Table',
find(upper('Keyword Table'[Narrative Contains]),upper('Transaction list'[Narrative]),,0))
>0,"Match","No Match")

Which gives me this result:

But I still can't figure out how to return the corresponding category...  

[robbieinoz · Accepted solution]

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...

[Anonymous]

Thanks, it works for me. Perfect

[MarioMazzoli]

Hi,

I'm using your solution on a similar DB.

My only issue is that I don't know how to customized the matching algortithm to include only exact matches. For example, one of my keyword is "pers" but this formula gives a match also when "personnel" is found. Do you know how I can request exact matches?

Thanks!