Forum Discussion

bmc892's avatar
bmc892
Regular Visitor
5 years ago
Solved

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.

Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    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

     

3 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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 

     

     

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    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

     

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    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