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 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 IDTest Scenario TypeTest Case StatusTest Case Result
MP1.01FunctionalityNot Started 
MP1.02FunctionalityNot Started 
MP1.03FunctionalityNot Started 
MP2.01ContentCompletePass
MP2.02ContentCompletePass
MP2.03ContentCompletePass
MP2.04FunctionalityCompletePass
MP2.05FunctionalityCompletePass
MP2.06FunctionalityCompletePass
MP2.07FunctionalityCompletePass
MP2.08FunctionalityCompletePass
MP3.01FunctionalityCompletePass
MP3.02FunctionalityCompletePass
MP4.01ContentCompletePass
MP4.02ContentCompletePass
MP4.03ContentCompletePass
MP4.04ContentCompletePass
MP5.01FunctionalityCompletePass
MP6.01FunctionalityCompletePass
MP7.01FunctionalityCompletePass
MP8.01FunctionalityCompletePass
MP8.02PricingCompletePass
MP8.03PricingCompletePass
MP8.04PricingCompletePass
MP8.05PricingCompletePass
MP8.06PricingCompletePass

 

I need to transform this into a new table - 

Test TypeTotalNot StartedCompletePassFail
Functionality14311110
Content

7

0770
Pricing50550

 

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!

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

     

     

     

4 Replies

  • 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!

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    3 years ago

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

      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.