Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Breaking Data into Categories using a text string in the field

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. 

Struktv_0-1642065039988.png

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

Struktv_1-1642065237232.png

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. 

2 REPLIES 2
v-jingzhang
Community Support
Community Support

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
Impactful Individual
Impactful Individual

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.