Grouping

[Anonymous]

Hi there,

I am trying to group using a dataset similar to the example that i have set out below, i am trying create a column that says for each squad, how many different countries resources are based in, i seem to be struggling to do this at the moment using the group function, the grouping that i create shows me the breakdown of the number of people in each countries, rather than just the total number of different locations that resources are based in.  Using the rather simplistic data example i have set out below i would like to create a grouping that has two rows, one for Apple with the count showing 3 (representing the number of locations for that squad) and one for orange showing 1 (representing the number of locations) Any suggestions? 

Worker ID	Squad Name	Location
1	Apple	UK
2	Apple	UK
3	Apple	Poland
4	Apple	US
5	Apple	US
6	Apple	US
7	Orange	UK
8	Orange	UK
9	Orange	UK
10	Orange	UK
11	Orange	UK
12	Orange	UK

[Ashish_Mathur]

Hi,

This m code works with just a single grouping

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Squad Name"}, {{"Count", each List.Count(List.Distinct([Location])), Int64.Type}})
in
    #"Grouped Rows"

Hope this helps.

[Nathaniel_C]

Hi @Anonymous ,
You are very welcome!

Nathaniel

[Nathaniel_C · Accepted solution]

Hi @Anonymous ,
Go to Power Query, use Group By Twice.

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
Original table

First grouping

Second grouping

Add this to Advanced Editor to create the above table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs8vyk4tUvB0UdJRCi4sTUxR8EvMTQVyfPKTE0sy8/OUYnWilQyBAo4FBTkgiVBvsJARppAxklBAfk5iXgpY2ARZZTBYyBRTyAxTyBzI9C9KzEtHssMCi5glFjFDA2yChtgEjdAFYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Worker ID", Int64.Type}, {"Squad Name", type text}, {"Location", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Squad Name", "Location"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Squad Name"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows1",{{"Count", "Number of Countries"}})
in
    #"Renamed Columns"