Forum Discussion

arjunsk's avatar
arjunsk
New Member
9 years ago
Solved

To count the splitted column

Hi All, I have splitted the string to multiple columns by Edit Query option. I just want to get count of column that has value. See below example of table splitting.   Can someone help on t...
  • ImkeF's avatar
    ImkeF
    9 years ago

    Agree with Anonymous, but if his suggestion is not option, a new column with this formula would do the trick:

    List.NonNullCount(Record.FieldValues(_))
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi arjunsk,

     

    I agree with pawelpo's point of view, you can add a column to store the count of list which split your cell text with particular separator.

     

    For example:

        #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Count Item", each List.Count(Text.Split([Column],",")))

     

     

    Reference:

     

    Function Description
    List.Count Returns the number of items in a list.
    Text.Split Returns a list containing parts of a text value that are delimited by a separator text value.

     

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi arjunsk,

 

I agree with pawelpo's point of view, you can add a column to store the count of list which split your cell text with particular separator.

 

For example:

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Count Item", each List.Count(Text.Split([Column],",")))

 

 

Reference:

 

Function Description
List.Count Returns the number of items in a list.
Text.Split Returns a list containing parts of a text value that are delimited by a separator text value.

 

 

Regards,

Xiaoxin Sheng