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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
bmc892
Regular Visitor

Help with splitting customer type data in power BI

Hi,

I am pulling account/customer data from salesforce.  We have a field in salesforce that asks the user to enter the type of cutomer theyre dealing with. If they select multiple the data looks like this in PowerBI, "Resteraunts;shops;hotel"
Each type is split by a semi colon.

My query is that i need to see how many accounts deal with resteraunts and how many are shops, for example.

If i have the data in a whole number i can then calcularte thinks like how many shops have been contacted in a certain area.

 

This is probably very badly explained but any help will be appreciated.

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

Hello @bmc892 

 

you can create a new column or a measure for each type. A measure could looking something like this

CountHotel = SUMX(AddColumns(YourTable,"ItsaHotel",if(Search("Hotel",YourTable[ColumnOfType])>0,1,0)), [ItsaHotel])

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

View solution in original post

3 REPLIES 3
PhilipTreacy
Super User
Super User

Hi @bmc892 

Without your actual data I can only give a limited example, you can download my sample PBIX file here.

But what you can do with a column of text like this

customer-type.png

is split it by delimiter ; and create a new row for each word

split-col-dialog.png

split-col.png

 

Capitalise each word so that hotel and Hotel don't get counted as different words.  Then group the rows to give you a count for each type

count-rows.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Jimmy801
Community Champion
Community Champion

Hello @bmc892 

 

you can create a new column or a measure for each type. A measure could looking something like this

CountHotel = SUMX(AddColumns(YourTable,"ItsaHotel",if(Search("Hotel",YourTable[ColumnOfType])>0,1,0)), [ItsaHotel])

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

AlB
Super User
Super User

Hi @bmc892 

Can you provide an example based on data of what you exactly need? With the expected result please

I don't understand if you just want help with splitting that column in several ones, with  calculating how many restaurants etc

If the selection is Restaurants;shops;hotel" would you count one for each?

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors