Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
cferv_77
Helper I
Helper I

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

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?  

IDDeptClass 1Class 2Class 3Class 4Class 5# of 2023 Classes
12Admin 1/1/2023 4/1/2023 8/1/2022 10/1/2022 12/1/2022 2
13Admin 1/1/2023 9/1/2022 4/1/2023 10/1/2022 6/1/2023 3
2 ACCEPTED SOLUTIONS
m_dekorte
Super User
Super User

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

m_dekorte_0-1682028725204.png

 

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

View solution in original post

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!

View solution in original post

3 REPLIES 3
m_dekorte
Super User
Super User

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

m_dekorte_0-1682028725204.png

 

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

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. 

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!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors