Check if rows contain a string value against an unrelated table

[sohrabsa]

Hi,

I have two tables in my dataset:

Table1 is my transactions which have a description that looks like this:

and Table2 is my legend table, where I define what each transaction belong to what category:

My goal is to categorize each transaction based on the legend by looking for a piece of description column in the transaction column. So if a transaction contains "Desk", the column I'm going to create shows "Office Supplies".

Here is what I've tried:

I used a new custom column in Table 1 to check for 3 values and return true if they returned a value:

List.Transform ( {"Desk", "Payroll", "Chair"}, (substring) => Text.Contains([Description], substring))

This returns a list of TRUE or FALSE based on the three examples I gave it. Potentially this can be used to get the first row that returns true and use that as a reference for the second column?

I tried to do it in Excel, and essentially I had to use SEARCH fonction with INDEX and MATCH to get the category from a different table. My issue is that I have two tables that are not related and I'm not sure how to do a search for a piece of string for every row.

I'm pretty new to PowerBI and I need some support please.

TIA!

[CNENFRNL · Accepted solution]

let
    Legend = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkisLMrPyVHSUQpOzEksqlSK1YlWcs5IzCwCCvmnpWUmpyoElxYU5GSmFoPlXFKLs3FIOefn5mYWF2fm56EZl5+WlpqKQ5NTfl5pMZL6WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Txn = _t, Category = _t]),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LctBCoAgEADAv+zZw7pr4TO6i8SaQZEkaR36fQZeB8Y50MRxrQeTBgUaEbxyQGwmeUtOSXNj6iwiyyZ7CUxNuevfzRBrI9MJMeTzqfNV7qajteD9Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Desc = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Desc", type text}, {"Amount", Int64.Type}}),
    Categorized = let txn = Legend[Txn], cat = Legend[Category] in Table.AddColumn(#"Changed Type", "Cat.", each cat{List.PositionOf(txn, [Desc], 1, (x,y) => Text.Contains(y,x,Comparer.OrdinalIgnoreCase))})
in
    Categorized

[sohrabsa]

This does the job! 

At first, I was confused by your first two lines, but then I realized that's just the way you loaded the data. For me it was from an Access Database and an Excel sheet.

Essentially the formula I used is: 

 Categorized = let txn = Legend[Txn], cat = Legend[Category] in Table.AddColumn(#"Changed Type", "Cat.", each cat{List.PositionOf(txn, [Desc], 1, (x,y) => Text.Contains(y,x,Comparer.OrdinalIgnoreCase))})

And then I had to change "Legend" with the latest table and the latest columns. 

Thank you so much!

[tamerj1]

Hi @sohrabsa 

You can create a calculated column in table 2

SUMX ( 

FILTER ( Table1, CONTAINSSTRING ( Table1[Description], Table2[transaction] ) ),

Table1[Amount]
)

[sohrabsa]

This also worked, but it was not exactly what I was looking for. Thank you for your time!