Forum Discussion

villa1980's avatar
villa1980
Resolver II
1 year ago
Solved

Power Query List Count

Hi all,

 Hope this is a quick one, so I have created a Custom Column that counts the number of text instances within a cell separated by ",".
 This works fine, however, it is counting blank cells which I don't want.
Any ideas what I add to the formula for this?

List.Count(Text.Split([Please check and confirm below you have all necessary PPE equipment available], ","))

 

 

Thanks


Alex

Need Help
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    Hi villa1980  try this M code:

    = try List.Count(
    List.Select(
        Text.Split([Text], ","),
        each _ <> "" and _ <> null
    )
) otherwise ""

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution!!

     

    Best Regards,
    Shahariar Hafiz

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    Hi, use List.Select to filter the list to include only non-blank values:
    Than you

    List.Count(List.Select(Text.Split([Please check and confirm below you have all necessary PPE equipment available], ","), each _ <> ""))

     

  • villa1980's avatar
    villa1980
    1 year ago

    That's great, thank-you both for the replies, both versions worked for me.

3 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi villa1980  try this M code:

    = try List.Count(
    List.Select(
        Text.Split([Text], ","),
        each _ <> "" and _ <> null
    )
) otherwise ""

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution!!

     

    Best Regards,
    Shahariar Hafiz

    • villa1980's avatar
      villa1980
      Resolver II
      1 year ago

      That's great, thank-you both for the replies, both versions worked for me.

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi, use List.Select to filter the list to include only non-blank values:
    Than you

    List.Count(List.Select(Text.Split([Please check and confirm below you have all necessary PPE equipment available], ","), each _ <> ""))