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.

Guido_Beulen's avatar
Guido_Beulen
Helper I
2 years ago

Add a column in Power Query M with: 

= Table.AddColumn(#"[laststep]", "Count separated values", each Text.Length([ColumWhereToCountSeparatedValues])-Text.Length(Replacer.ReplaceText([ColumWhereToCountSeparatedValues],";",""))+1)

 

In this example the seperator is ";"

 

The DAX variation in form of a measure is: 

CountOfItems = LEN(MAX(List[List])) - LEN(SUBSTITUTE(MAX(List[List]),",","")) + 1

 Which was discussed in this topic.