Counting the # of columns that contain a specific item in PowerQuery

[cferv_77]

Hello, 

I have a table that I am trying to get another of columns that has a specific item. In this case, I am counting the number of columns that have the year 2023. I do not want to convert the column to years. Is this possible in PowerQuery?  

ID	Dept	Class 1	Class 2	Class 3	Class 4	Class 5	# of 2023 Classes
12	Admin	1/1/2023	4/1/2023	8/1/2022	10/1/2022	12/1/2022	2
13	Admin	1/1/2023	9/1/2022	4/1/2023	10/1/2022	6/1/2023	3

[m_dekorte · Accepted solution]

Hi @cferv_77 

See if this is helpful.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRS0lFyTMnNzAPSCob6hvpGBkbGILYJEtsCwjYCqzFA5hghcYyUYnWAJhrjMtESSS2y6SgmmiFJGCvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Dept = _t, #"Class 1" = _t, #"Class 2" = _t, #"Class 3" = _t, #"Class 4" = _t, #"Class 5" = _t, #"# of 2023 Classes" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Class 1", type date}, {"Class 2", type date}, {"Class 3", type date}, {"Class 4", type date}, {"Class 5", type date}}),
    AddClassCount = Table.AddColumn( ChType, "Count Classes 2023", each 
        List.Count( List.Select(
                List.Transform( 
                    Record.ToList( 
                        Record.SelectFields( _, 
                            List.Select( Record.FieldNames( _ ), each Text.Contains( _, "Class " ))
                        )
                    ), Date.Year 
                ), (x)=> x= 2023 
            )))
in
    AddClassCount

Note that I'm dynamically selecting all record field names that have the string "Class " in their name. You can replace that with a static list of names, if these are fixed.

Or alternatively replace that last step with this code, that will check all field from the table row

    AddClassCount = Table.AddColumn( AddClassCount, "Count Classes 2023", each 
        List.Count( List.Select(
                List.Transform( Record.ToList( _ ), (v)=> try Date.Year(v) otherwise 0
                ), (x)=> x= 2023 
            )))

The result is this added column to your table

Ps. Please mark this answer as solution when it helped you to resolve your question, thanks!

[m_dekorte · Accepted solution]

Hi @cferv_77 

See if this is helpful.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRS0lFyTMnNzAPSCob6hvpGBkbGILYJEtsCwjYCqzFA5hghcYyUYnWAJhrjMtESSS2y6SgmmiFJGCvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Dept = _t, #"Class 1" = _t, #"Class 2" = _t, #"Class 3" = _t, #"Class 4" = _t, #"Class 5" = _t, #"# of 2023 Classes" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Class 1", type date}, {"Class 2", type date}, {"Class 3", type date}, {"Class 4", type date}, {"Class 5", type date}}),
    AddClassCount = Table.AddColumn( ChType, "Count Classes 2023", each 
        List.Count( List.Select(
                List.Transform( 
                    Record.ToList( 
                        Record.SelectFields( _, 
                            List.Select( Record.FieldNames( _ ), each Text.Contains( _, "Class " ))
                        )
                    ), Date.Year 
                ), (x)=> x= 2023 
            )))
in
    AddClassCount

Note that I'm dynamically selecting all record field names that have the string "Class " in their name. You can replace that with a static list of names, if these are fixed.

Or alternatively replace that last step with this code, that will check all field from the table row

    AddClassCount = Table.AddColumn( AddClassCount, "Count Classes 2023", each 
        List.Count( List.Select(
                List.Transform( Record.ToList( _ ), (v)=> try Date.Year(v) otherwise 0
                ), (x)=> x= 2023 
            )))

The result is this added column to your table

Ps. Please mark this answer as solution when it helped you to resolve your question, thanks!

[cferv_77]

Thank you @m_dekorte. I had to modify the code a bit since I using column numbers as referrences. 

#"AddClassCount" = Table.AddColumn(#"Renamed Columns", "Count Classes 2023", each
    List.Count( List.Select(
        List.Transform(
            List.Range(Record.FieldValues(_),20,5)
            , Date.Year
            ),
        (x)=> x= 2023
 )))

I have another question. How can I make that whole column changed to the Maximum number of classes found in 2023? I tried wrapping the code in a List.Max but doesn't work. 

[m_dekorte · Accepted solution]

Hi @cferv_77 

To get the max value from the column, you need to return that columns values as a list.

I've placed that calculation outside of the table because it only needs to be determined once, next you can replace all column values if you want that value to be repeated for each row in the table. Alternatively you could also add a new column with this value and delete the old column, whatever makes the most sense to you.

    #"AddClassCount" = Table.AddColumn(#"Renamed Columns", "Count Classes 2023", each
        List.Count( List.Select(
            List.Transform(
                List.Range(Record.FieldValues(_),20,5)
                , Date.Year
                ),
            (x)=> x= 2023
     ))),
    MaxColValue = List.Max( #"AddClassCount"[Count Classes 2023] ),
    UpdateColValue = Table.ReplaceValue(#"AddClassCount",each [Count Classes 2023], each MaxColValue, Replacer.ReplaceValue,{"Count Classes 2023"})

Ps. Please mark this answer as solution when it helped you to resolve your question, thanks!