Forum Discussion
Counting Number of Elements in Array
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?
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
5 Replies
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/
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/222486I think that the solution could be much easier , to count number of delimiters within the string + 1.
How I do it?
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 _ JaneySure, thank you!
