How do one count how many text values contain in row in powerquery

val 0
Viewed 55

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:

B1 B2 B4 count of text
1003 A2 A1 2
1010 D D 2
1004 A2 2 1
1007 B2 B4 2
1009 0
1003 A2 A1 2
BCD C 2
1004 A2 2 1

The challenge is how do I achieve this using powerquery.

Thank you.

powerquery excel
2 Answers

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"
Umut K0

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
horseyride0
Related