To count the splitted column

[arjunsk]

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 this?

[ImkeF · Accepted solution]

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 · Accepted solution]

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]

In my opinion, it's not the best idea to split the data into columns when you have multiple items in a single column. Why not split strings into rows? (this option is also available in Power BI Desktop transformations) Then you could perform a simple COUNT for each issue row to find the number of related issues.

[ImkeF · Accepted solution]

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

List.NonNullCount(Record.FieldValues(_))