Counting Number of Elements in Array

[joe100]

Having two columns: 

NUMBER	ID
ABC015753	0001612224-25-1,0001612224-25-2,0001612224-25-3,0001612224-25-4
ABC015761	EBC257D 85C16327 599F8358  CC990AEF  64CD90C5
ABC015787	56567ZXC; 687687687BV; 8787687CVX

I wish to add a new column where will be presented number of element from column "ID".

The delimiters can be "," , " " , ";".

What's the best way to do this?

[joe100]

I think that the solution could be much easier , to count number of delimiters within the string + 1.

How I do it?

[v-janeyg-msft · Accepted solution]

Hi, @joe100 

You can create a custom column in pq.

Like this:

List.Count(Text.PositionOfAny([ID], {","," ",";"}, -1))

If you need to add newlines, you can use ‘#lf’:

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[joe100]

Sure, thank you!

[ValtteriN]

Hi,

You can split columns by delimiter in powerquery by right clicking a column, but this won't let you to split by multiple delimiters. Chek this post on how to do this: https://community.powerbi.com/t5/Desktop/splitting-multiple-delimiter-in-column-amp-find-max/m-p/222...

[amitchandak]

@joe100 , I think you need split by a delimiter in power query, more than one (Into rows)

Split Column Power Query: https://youtu.be/FyO9Vmhcfag

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/