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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors