Power Query List Count

[villa1980]

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

[shafiz_p · Accepted solution]

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 · Accepted solution]

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 _ <> ""))

[Bibiano_Geraldo · Accepted solution]

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 _ <> ""))

[shafiz_p · Accepted solution]

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 · Accepted solution]

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