Power query editor

[AtchayaP]

i have this table in my power query, how can I count multiple values in one cell in power query editor .for example  skill count for first row should be 8.can anyone help me how to do this?   @amitchandra @parry2k 

[Anonymous · Accepted solution]

Hi  @AtchayaP ,

Here are the steps you can follow：

In Power Query..

1. Add Column – Custom Column .

2. Enter the following.

Text.Length([skills])

-

List.Count(Text.PositionOf([skills],",",Occurrence.All ))

3. Result:

Use Dax：

Create calculated column.

LEN(SUBSTITUTE('Table'[skills],",",""))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Count(Text.Split([skills],",")))
in
    #"Added Custom"

Hope this helps.

[Anonymous · Accepted solution]

Hi  @AtchayaP ,

Here are the steps you can follow：

In Power Query..

1. Add Column – Custom Column .

2. Enter the following.

Text.Length([skills])

-

List.Count(Text.PositionOf([skills],",",Occurrence.All ))

3. Result:

Use Dax：

Create calculated column.

LEN(SUBSTITUTE('Table'[skills],",",""))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[HotChilli]

You could use Text.Split to turn it into a list, then use List.Count

or

if you can rely on it, you can count the occurrences of "," and add 1

[AtchayaP]

Sure.should I use dax or any other?