Forum Discussion

borstroem's avatar
borstroem
Regular Visitor
3 years ago
Solved

Count columns which contain text

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 ...
Need Help
  • serpiva64's avatar
    serpiva64
    3 years ago

    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!

latimeria's avatar
latimeria
Solution Specialist
3 years ago

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"