Forum Discussion
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 this?
Agree with Anonymous, but if his suggestion is not option, a new column with this formula would do the trick:
List.NonNullCount(Record.FieldValues(_))
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
3 Replies
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.
Agree with Anonymous, but if his suggestion is not option, a new column with this formula would do the trick:
List.NonNullCount(Record.FieldValues(_))
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