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:

 

IDXYZCells with text
1AppleOrangeBanana3
2BananaApple 2
3 Banana 1
4   0
5  Apple1
6OrangeApple 2

 

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

 

Thanks a lot in advance 🙂

 

 

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!

2 Replies

  • serpiva64's avatar
    serpiva64
    Solution Sage
    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"