March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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 |
Solved! Go to 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!
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!
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!
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.