Keep information not included in columns used in "Group by"

[AriBouaniche]

Hi,

I'm sorry if this is rather elementary, but I cannot figure how to make PowerQuery do what I want...

I have a table which looks like this:

Last name	First name	Maiden name	Other columns...	Contracts
LNPerson1	FNPerson1	MNPerson1	...	0
LNPerson2	FNPerson2	MNPerson2	...	1
LNPerson2	FNPerson2	MNPerson2	...	0
LNPerson3	FNPerson3	MNPerson3	...	0
LNPerson3	FNPerson3	MNPerson3	...	1
LNPerson3	FNPerson3	MNPerson3	...	1
LNPerson3	FNPerson3	MNPerson3	...	0

I have, in the same step, grouped the table by the first three columns (Last name, First name, and Maiden name, to make sure I get a unique person), and then implemented a custom column with a function which finds out whether a person is not employed (sum of "Contracts" column for that person = 0), is employed once (sum of "Contracts" = 1), or is employed multiple times (sum of "Contracts" > 1), with M code that looks like:

GroupedRows = Table.Group(Source, {"Last_name", "First_name", "Maiden_name"}, {"Employed", (x) => if List.Sum(Table.Column (x, "Contracts")) > 1 then "Multiple" else if List.Sum(Table.Column (x, "Contracts")) = 1 then "Once" else "No"})

This, I can do without a problem. What I can't figure out, is how to get PowerQuery to include the info included in the other columns of my original table (which comprise the place of employment, for example), keeping only the first row featuring a "1" in the "Contracts" column. To phrase it differently, I need to add information back from the original table to the table I have just created (unique person + employment status): the first row which is indeed a contract (so with a "1" in said column) lists the main place of employment, so this is the one I want to keep, even in the case of a "Multiple" employment case.

My problem is that if I group my original table by more columns, I am going to get several grouped rows for one and the same person, which defeats the point... (I hope I'm being clear, here...)

How do I go about doing this?

Any help will be greatly appreciated!

Thank you,

Ari  ;o)

[Anonymous]

Hi,

Thanks for the solutions mussaenda, Omid_Motamedise and OwenAuger offered, and i want to offer some more information for user to refet to.

hello @AriBouaniche , you can consdier to add a custom column directly. you can refer to the following code to advanded editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vELSC0qzs8zVNJRckNi+yKxDZRidRAqjZBUGiGpBLENiVaJaqYxkkpjJJXGJKk0pIFKoO2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Last name" = _t, #"First name" = _t, #"Maiden name" = _t, Contracts = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Last name", type text}, {"First name", type text}, {"Maiden name", type text},{"Contracts", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a=[Last name],
b=[First name],
c=[Maiden name],
d=List.Sum(Table.SelectRows(#"Changed Type",each [Last name]=a and [First name]=b and [Maiden name]=c)[Contracts])
in if d>1 then "Multiplt" else if d=1 then "Once" else "No")
in
    #"Added Custom"

Output

Best Regards!

Yolo Zhu

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

[mussaenda]

Hi @AriBouaniche ,

When grouping, you can select advanced option, then in operation, you can select 'All Rows'. 

Then you can add the columns that you need.

Hope this helps

[Omid_Motamedise]

See this video for what you need to know about table.group

https://www.youtube.com/watch?v=Nh0Ch-fQc_k&t=35s

[OwenAuger · Accepted solution]

Hi @AriBouaniche 

Here's a small example in a standalone query to illustrate one possible method.

I adjusted the code for the "Employed" column slightly, and added an aggregated column "FirstContractRow" containing the "first" row of the original table with Contracts = 1.

In this case, "first" means the first row based on Power Query's ordering of rows. You might want to consider adding logic based on an appropriate sorting column if one exists.

let
  Source = #table(
    type table [
      Last_name = text,
      First_name = text,
      Maiden_name = text,
      Place of Employment = text,
      Contract Terms = text,
      Contracts = Int64.Type
    ],
    {
      {"LNPerson1", "FNPerson1", "MNPerson1", null, null, 0},
      {"LNPerson2", "FNPerson2", "MNPerson2", "Solla Sollew", "Fixed Price", 1},
      {"LNPerson2", "FNPerson2", "MNPerson2", null, null, 0},
      {"LNPerson3", "FNPerson3", "MNPerson3", null, null, 0},
      {"LNPerson3", "FNPerson3", "MNPerson3", "Whoville", "Time and Materials", 1},
      {"LNPerson3", "FNPerson3", "MNPerson3", "Jungle of Nool", "Fixed Price", 1},
      {"LNPerson3", "FNPerson3", "MNPerson3", null, null, 0}
    }
  ),
  GroupedRows = Table.Group(
    Source,
    {"Last_name", "First_name", "Maiden_name"},
    {
      {
        "Employed",
        each
          let
            ContractsSum = List.Sum([Contracts])
          in
            if ContractsSum > 1 then "Multiple" else if ContractsSum = 1 then "Once" else "No",
        type text
      },
      {
        "FirstContractRow",
        each Table.FirstN(Table.SelectRows(_, each [Contracts] = 1),1),
        Value.Type(Source)
      }
    }
  ),
  ExpandedColumns = Table.ExpandTableColumn(
    GroupedRows,
    "FirstContractRow",
    {"Place of Employment", "Contract Terms"},
    {"Place of Employment", "Contract Terms"}
  )
in
  ExpandedColumns

Is this the sort of thing you were looking for?

[AriBouaniche]

Oh my God, thank you thank you thank you!! With your code I was able to do what I intended and more! I had no idea that other let / in sequences could be included in the middle of existing code, which simplifies everything for me!

That's just great!