Forum Discussion

SenselessNoise's avatar
SenselessNoise
New Member
3 years ago
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...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    3 years ago

    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

     

     

     

ronrsnfld's avatar
ronrsnfld
Super User
3 years ago

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's avatar
SenselessNoise
New Member
3 years ago

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!