Breaking Data into Categories using a text string in the field

[Anonymous]

So im fairly new to PowerBI and have a spreadsheet I am trying to replicate the function of. 

Its a finance sheet that does expense tracking. I load all of my monthly or yearly transactions into it from my bank statement and it breaks all of the transactions up into columns depending on which category the vendor is in as per below using Excel Lists. 

This is a subset of the lists that are stored in their own tab. 

This is a subset of the transactions sheet and how it categorises the data. This is in another tab. 

I can re-create most of it by creating a category and sub-category table and joining them with the transaction table but the issue is the Search or Find function that I use to join the find the correct category and join them in Excel which is this:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(Lists!B$2:$B$3,$B22))),$D22,"")

Just wondering how I would replicate that in PowerBI? I just want it to search the description field in the Transactions table for one of the vendors in the categories table and return the corresponding category. 

I am not worried about breaking it up into columns as I can do that in the report section later. Its just the search and return I am having issues with. 

[v-jingzhang]

Hi @Anonymous 

Can you help explain what does IF(SUMPRODUCT(--ISNUMBER(SEARCH(Lists!B$2:$B$3,$B22))),$D22,"") do? Where is this formula used and what result does it get? Currently I don't understand what sample data is used in cells like B2, D22, B22, B3. Can you explain the expected output with some sample data? Paste sample data in table format in the post so that we can use it to create a sample file. 

Best Regards,
Community Support Team _ Jing

[freginier]

You can use SUMX function in Power BI it's like SUMPRODUCT