How do one count rows in powerquery table that contain just text values-CodePudding

Good day all,how can one count rows in a table in powerquery containing just text values.

I want my table in this format.

Mail	listern	Boby	TOTAL
B	1	C	2
B	0	3	1

CodePudding user response：

 let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclJQ0lEyBBHOCkqxOlABAxBhDBSIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Mail " = _t, #"listern " = _t, #"Boby " = _t]),
        #"Added Custom" = Table.AddColumn(Source, "Custom", each 
        let a = Record.ToList(_),
        b = List.Transform(a, each try Number.From(_) otherwise "Text" ),
        c = List.Select(b, each _ = "Text" ),
        d = List.Count(c)
        in d,  Int64.Type)
    in
        #"Added Custom"

CodePudding user response：

Another way in PowerQuery

#"Added Custom" = Table.AddColumn("#PriorStepNameHere", "TOTAL", each  List.Sum(List.Transform(Record.ToList(_), each try Number.From(_)-Number.From(_) otherwise 1)))

CodePudding user response：

If you wish you can do it by formula.

=SUM(--ISTEXT(A2:C2))