GROUP BY and COUNTIFS

[SenselessNoise]

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 close to this use GROUP BY and COUNTIF, but I really need to use COUNTIFS. 

My source data looks like this (there's more data but it's proprietary and not really important to my question) - 

Test Scenario ID	Test Scenario Type	Test Case Status	Test Case Result
MP1.01	Functionality	Not Started
MP1.02	Functionality	Not Started
MP1.03	Functionality	Not Started
MP2.01	Content	Complete	Pass
MP2.02	Content	Complete	Pass
MP2.03	Content	Complete	Pass
MP2.04	Functionality	Complete	Pass
MP2.05	Functionality	Complete	Pass
MP2.06	Functionality	Complete	Pass
MP2.07	Functionality	Complete	Pass
MP2.08	Functionality	Complete	Pass
MP3.01	Functionality	Complete	Pass
MP3.02	Functionality	Complete	Pass
MP4.01	Content	Complete	Pass
MP4.02	Content	Complete	Pass
MP4.03	Content	Complete	Pass
MP4.04	Content	Complete	Pass
MP5.01	Functionality	Complete	Pass
MP6.01	Functionality	Complete	Pass
MP7.01	Functionality	Complete	Pass
MP8.01	Functionality	Complete	Pass
MP8.02	Pricing	Complete	Pass
MP8.03	Pricing	Complete	Pass
MP8.04	Pricing	Complete	Pass
MP8.05	Pricing	Complete	Pass
MP8.06	Pricing	Complete	Pass

I need to transform this into a new table - 

Test Type	Total	Not Started	Complete	Pass	Fail
Functionality	14	3	11	11	0
Content	7	0	7	7	0
Pricing	5	0	5	5	0

When I try to use Group By, I can no longer reference the source data to create the additional columns.  I also can't seem to figure out the conditional column structure/formulas to refer back to the original source data for the COUNTIFS structure (eg. COUNTIFS type = Functionality, Status = Not Started).  Can anyone point me in the right direction to figure this out?  THANK YOU!

[Nathaniel_C]

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

[SenselessNoise]

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.

[ronrsnfld · Accepted solution]

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

[SenselessNoise]

Thank you!  This is exactly what I needed.  I'm new to this language but I have a lot of VBA experience and this really helps me understand some more "advanced" coding options.  Much appreciated!