Forum Discussion
Solved
GROUP BY and COUNTIFS
Hi All, Thanks in advance for the assistance - I am extremely new to Power Query, and I've been looking for the last 3 hours for a solution to my issue. The majority of posts I've found remotely...
You can write the aggregations for the Table.Group function in the Advanced Editor manually.
let //Change next line to reflect actual data source Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Test Scenario ID", type text}, {"Test Scenario Type", type text}, {"Test Case Status", type text}, {"Test Case Result", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Test Scenario Type"}, { {"Total", each Table.RowCount(_), Int64.Type}, {"Not Started", (t)=> List.Count(List.Select(t[Test Case Status], each _ = "Not Started")), Int64.Type}, {"Complete", (t)=>List.Count(List.Select(t[Test Case Status], each _ = "Complete")), Int64.Type}, {"Pass", (t)=>List.Count(List.Select(t[Test Case Result], each _ = "Pass")), Int64.Type}, {"Fail", (t)=>List.Count(List.Select(t[Test Case Result], each _ = "Fail")), Int64.Type} }) in #"Grouped Rows"Your Data
Results
Hi SenselessNoise ,
You are on the right track. However you must set your conditional columns first, and finally aggregate by summing on those columns.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldE9C8IwEIDhv1IyF7H5anfBTQk4lg6hBgnUpLTn4L83WsElrXfbHTzDJW/bspOpdvuKlez4CD34GOzg4Zn2c4TiAnYCd01bwbryizkFCzTmyxmHGMAF+Ez3cXDg0mjsPP8YxzGBYzJz4SpWFKwpuKbgBo1FPu4qzsXNY4nrJXG9JK6XXHr9Y4ryak3BNQU3RPz+KDP53ofbFhM4JnFM4ZjeZt0L", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"test Scenario ID" = _t, #"Test Scenario Type" = _t, #"Test Case Status" = _t, #"Test Case Result" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"test Scenario ID", type text}, {"Test Scenario Type", type text}, {"Test Case Status", type text}, {"Test Case Result", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Not started", each if Text.Contains([Test Case Status], "Not Started") then 1 else 0),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Completed", each if [Test Case Status] = "Complete" then 1 else 0),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Pass", each if[Test Case Result] = "Pass" then 1 else 0),
#"Added Conditional Column1" = Table.AddColumn(#"Added Custom1", "Fail", each if Text.Contains([Test Case Result], "Fail") then 1 else 0),
#"Grouped Rows" = Table.Group(#"Added Conditional Column1", {"Test Scenario Type"}, {{"Total", each Table.RowCount(_), Int64.Type}, {"Not Started", each List.Sum([Not started]), type number}, {"Complete", each List.Sum([Completed]), type number}, {"Pass", each List.Sum([Pass]), type number}, {"Fail", each List.Sum([Fail]), type number}})
in
#"Grouped Rows"You can paste this into the advanced editor.
Basically follow this pattern.
And then groupby
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Thank you Nathaniel! I appreciate the explanation. I figured I needed to cut the columns down before the grouping to make it work but I understand now I should've done the opposite.