Forum Discussion

WakeTurbulence's avatar
WakeTurbulence
Frequent Visitor
8 years ago
Solved

Count comma delimited data

Hello,   I have a data dump whereby some fields can have more than one type in the same occurrence and other fields are multi-select which can give multiple categories in the same occurrence. I wan...
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    I do not have a fancy way to solve this.  All i can suggest is that we create two tables fro your source as shown below.  I have done so by using the Split column > By rows feature of Power Query.

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Do not split the data by columns.  SPlit it by rows as suggested in my previous post.

cibgks's avatar
cibgks
Regular Visitor
8 years ago

Did you find any solution? I have been trying to solve this exact problem for 1 week already

WakeTurbulence's avatar
WakeTurbulence
Frequent Visitor
8 years ago

Hi,

 

The solution from Ashish is pretty much it. Splitting the delimited data into rows works best. The id number will get duplicated but BI will accurately count each instance of the category.

 

For me this causes an unwanted duplication of all my other fields so my solutution for this is to split the category information out into a separate table that is linked to the host data table via the ID column.

 

I'm sure theres a better, more elegant way, but its working for me at this time until I sit down and try to work out a more elegant solution.

 

Wake

  • WakeTurbulence's avatar
    WakeTurbulence
    Frequent Visitor
    8 years ago

    I should add that I do this for each field which contains delimited data.

     

    I then have a measure which uses ROUNTROWS to get the total.

     

    TOTAL = COUNTROWS(tblCategory)