Group by - One is equal but other column is different

[IAM]

Hi!

I want to use a countifs function in Power Query, so I know I need to use group by, but how do I get the following:

Workorders -> need to be the same

Department -> need to be different 

Te goal is to regonize the rows that have the same workorder, but multiple departments working on it.

If anyone has a better way of achieving this, I am happy to hear it!

[Anonymous · Accepted solution]

Hi @IAM ,

If you want this:

Please try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWBsJzgLGc4yxGNZYTCckITM4awYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Workorders = _t, Department = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Workorders", Int64.Type}, {"Department", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Workorders"}, {{"Distinct Depart each WorkOrder", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"All", each _, type table [Workorders=nullable number, Department=nullable text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Department"}, {"Department"}),
    #"Grouped Rows1" = Table.Group(#"Expanded All", {"Workorders", "Department"}, {{"each Work and Depart ", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Workorders=nullable number, Distinct Depart each WorkOrder=number, Department=nullable text]}}),
    #"Expanded All1" = Table.ExpandTableColumn(#"Grouped Rows1", "All", {"Distinct Depart each WorkOrder"}, {"Distinct Depart each WorkOrder"})
in
    #"Expanded All1"

And according to this:

The goal is to regonize the rows that have the same workorder, but multiple departments working on it.

If you want to sort tables by multiple columns, please use Table.Sort() and then add an Index column:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vijay_A_Verma]

Select Workorders followed by Department and then GroupBy. See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWBsJzgLGxiCJYzmGUEV4eLBdQRCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Workorders = _t, Department = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Workorders", "Department"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

[IAM]

Thanks for your reply, this counts the rows where workorders and departments are the same, I need the count f

when workorder = the same, but department is NOT the same.

[Vijay_A_Verma]

For below table, can you show what output is expected or if you have any sample table which you can post and also post the result.

Workorders	Department
1	A
1	B
1	A
1	B
1	B
1	C
2	A
2	A
2	A
2	B