Good day all,Excel has a function called ISTEXT

So,if intend to get all the text containing in a record, I will use =SUM(..ISTEXT(B1:B4)) If my data range is B1:B4. As shown i below:

The challenge is how do I achieve this using powerquery.

Thank you.

CodePudding user response：

Umut has the right idea

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Count = Table.AddColumn(Source, "count of text", each List.Sum(List.Transform(List.RemoveNulls(Record.FieldValues(_)),each if Value.Is(_,type number) then 0 else 1)))
in Count

CodePudding user response：

you can use Value.Is

sample

if Value.Is([data],type number) 
then "Number"
else "Text"

you can use as below but if you have more "B" columns, then you should use a custom function...

let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Added Custom" = Table.AddColumn(Source, "Count Texts", each (if Value.Is([B1], type text) then 1 else 0 )  
    (if Value.Is([B2], type text) then 1 else 0 ) 
    (if Value.Is([B4], type text) then 1 else 0 ))
    in
        #"Added Custom"