how can I count all numbers under 15 across 5 columns that were split by a comma delim in Power BI?

[Will38]

I have a column that imported from a database where childrens ages where in a text field seperated by a comma.  I spilt the column by the deliminator and now have 5 columns.  I need to count all children under the age of 15.  I have tried using counta with a filter and the result was not accurate.  Any other suggestions?

[wdx223_Daniel · Accepted solution]

=let a=List.Select(Table.ColumNames(PreviousStepName),each Text.StartsWith(_,"ClientCustom_ChildrenAges")) in Table.AddColumn(PreviousStepName,"AgeLessThan15",each List.Count(List.Select(Record.ToList(Record.SelectFields(_,a)),each Number.From("0"&_)<15)))

[ronrsnfld]

Consider splitting by the delimiter into Rows instead of columns; then counting the number <15 is simpler

[AlexisOlson]

Unpivot those columns and then filter for < 15.

Here's a simplified example that you can paste into the Advanced Editor of a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIDYksgNjRSitUBCoHEDE2ABAiBRcygHBjfGIkfCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each [Value] < 15),
    #"Count Rows" = Table.RowCount(#"Filtered Rows")
in
    #"Count Rows"

Starting with

Unpivoting results in this:

This format makes it easy to count values less than 15 either in the query editor or with DAX.

[Will38]

Thank you very much for your response.  I only copied the 4 columns that I am trying to count when asking this question.  There are 10 other columns as part of this table.  How will unpivoting these effect the other columns.  There is a comparison of this count and another count in the columns not shown in my question. 

[AlexisOlson]

You should only unpivot the columns you want to do the counting over.

If your table started like this:

Then unpivoting the last four columns would look like this:

[Will38]

Thank you