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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Solution Specialist
Solution Specialist

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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