Help with splitting customer type data in power BI

[bmc892]

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.

[PhilipTreacy]

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

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

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

Regards

Phil

[Jimmy801 · Accepted solution]

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]

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