Custom Column Query

[BenGWeeks]

I would like to create a column that counts the number of items in a comma separated striong as follows:

However, the ProjectCount column is currently returning errors (and needs an IF statement added if the project string has no commas the count should be 1).

This then splits the cost based on how many projects for each row. I shall then be splitting/pivoting the table on the projects.

Any help much appreciated.

[BA_Pete · Accepted solution]

Hi @BenGWeeks ,

Try using this calculation instead:

if [Projects] = null then 0 else List.Count(List.PositionOf(Text.ToList([Projects]), ",", Occurrence.All)) + 1

Pete

[amitchandak]

@BenGWeeks , This power query code used in DAX new column. Please open data transformation and try it there.