Count columns which contain text

[borstroem]

Hi

I'm looking to create a new column which counts the columns which contains any text in that given row. Example:

ID	X	Y	Z	Cells with text
1	Apple	Orange	Banana	3
2	Banana	Apple		2
3		Banana		1
4				0
5			Apple	1
6	Orange	Apple		2

I guess I'm looking for a M equivalent of the excel function countif(row, "*")

Thanks a lot in advance 🙂

[latimeria]

Hi @borstroem ,

Another way, using list

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsKMhJBdL+RYl56SCGU2IeECrF6kQrGSG4CIVgGWMQA0kSLGgCEYRxTeFciFaQmBmyTQgTYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, X = _t, Y = _t, Z = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Z"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each List.Count(List.Select(List.Skip(Record.ToList(_),1), each _ <> "" and _ <> null )))
in
    #"Added Custom"

[serpiva64 · Accepted solution]

Hi,

you can obtain this

by applying the steps of the attached file

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!