Groupby Criteria, Power Query Excel

[ccarpent]

Hi

I am trying to GroupBy or merge my data based on ID_NO, then show if there is a Manager(S)and who are the Assitants, if any .  I dont always know if there is a Manager and how many, or and I dont know how many Assistants there may be. The key field is 'ID_NO', which is the main reason for sorting this information.  If there is more than one Manager, then there is no priorty as to who is listed first, this also applies to any Assistant regards any sorting order.

My Source Data:

Table 1, SourceData

ID_NO	NAME	ROLE
451213	A PERSON	ASSISTANT
451213	J SMITH	MANAGER
451213	JOE ARM	ASSISTANT
451213	FRED FLINT	ASSISTANT
451213	SAM ROCK	ASSISTANT
775544	PAUL WAF	MANAGER
775544	SIMON FRED	ASSISTANT
661245	KIM TODD	MANAGER
787978	CHARLIE WILCOX	MANAGER
787978	CHRIS TOM	MANAGER
224455	PAUL JONES	ASSISTANT

Trying to re-create or transform table1 to look like this  - Table2 Option

ID_NO	Role1Mger	Role2Mger	Role1Assistant	Role2Assistant	Role3Assistant	Role4Assistant
451213	J SMITH		A PERSON	JOE ARM	FRED FLINT	SAM ROCK
775544	PAUL WAF		SIMON FRED
661245	KIM TODD
787978	CHARLIE WILCOX	CHRIS TOM
224455	null		PAUL JONES

So for ever Manager and Assitant, a column is created for that user, if there is no Manager then the 'Role1Manager' will be empty.

Or, maybe a simpler solution is to sort the data as follows - Table3 Option - 

ID_NO	RoleMgr	RoleAssistant
451213	ASSISTANT	A PERSON, JOE ARM, FRED FLINT, SAM ROCK
775544	PAUL WAF	SIMON FRED
661245	KIM TODD
787978	CHARLIE WILCOX, CHRIS TOM
224455	null	PAUL JONES

Just combines any Managers or Assistants using a comma as seperator

I did come across this - Solved: Re: Grouping by three conditions - Microsoft Power BI Community

But only managed this far:

let
Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID_NO", Int64.Type}, {"NAME", type text}, {"ROLE", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID_NO"}, {{"All Rows", each _, type table}}),
#"Added Status" =
Table.AddColumn(#"Grouped Rows", "Status", each Table.Max(
Table.FromRecords(
{
Table.Max(
Table.SelectRows([All Rows], each [NAME] <> null),
each [ID_NO]
)
}
),
each [Record last updated]
)[ROLE])
,
#"Added Result" =
Table.AddColumn(#"Added Status", "Result", each Table.Max(
Table.FromRecords(
{
Table.Max(
Table.SelectRows([All Rows], each [NAME] <> ""),
each [ID_NO]
)
}
),
each [Record last updated]
)[ROLE])
in
#"Added Result"

Here is a snippet of the tables, visual for illustration purposes only:

Table design

Any help appciated.

Chris

[AlB · Accepted solution]

Hi@ccarpent 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY7NDoMgEIRfZcPZixTEHjeKFeXHsDQ2Mb7/axQPbVqMt8nMt7OzbUzImtc3VjGERUcK/pBEhhL6xPbqh5iAnEljVg49PnQs4qABo7u+H6LuYbAm+5cMoYMYuvlEKCWlENle8GlhxaGY8c3JuODh+HXqaJqaC5nt2ThIoe/LjlbdVZvNbsRojYbV2C68rqloKPe4AuBcCCk/U6fgNf1P2d8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_NO = _t, NAME = _t, ROLE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID_NO", Int64.Type}, {"NAME", type text}, {"ROLE", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID_NO", "ROLE"}, {{"Aux", each Text.Combine([NAME],",")}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[ROLE]), "ROLE", "Aux"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"ID_NO", "MANAGER", "ASSISTANT"})
in
    #"Reordered Columns"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY7NDoMgEIRfZcPZixTEHjeKFeXHsDQ2Mb7/axQPbVqMt8nMt7OzbUzImtc3VjGERUcK/pBEhhL6xPbqh5iAnEljVg49PnQs4qABo7u+H6LuYbAm+5cMoYMYuvlEKCWlENle8GlhxaGY8c3JuODh+HXqaJqaC5nt2ThIoe/LjlbdVZvNbsRojYbV2C68rqloKPe4AuBcCCk/U6fgNf1P2d8=", BinaryEncoding.Base64), Compression.Deflate)), 
    let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_NO = _t, NAME = _t, ROLE = _t]),
    RR = Table.Group(Source, {"ID_NO"}, {{"all", each Table.PromoteHeaders(Table.Transpose(_[[ROLE],[NAME]]))}}),
    #"Tabella all espansa" = Table.ExpandTableColumn(RR, "all", {"MANAGER", "MANAGER_1", "ASSISTANT", "ASSISTANT_1", "ASSISTANT_2", "ASSISTANT_3"})
in
    #"Tabella all espansa"

It seems to me that this problem has the same structure as the one discussed here 

[ccarpent]

Rocco

Thats really good, now discovering pivot columns and Table.Transpose.

thanks

[AlB · Accepted solution]

Hi@ccarpent 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY7NDoMgEIRfZcPZixTEHjeKFeXHsDQ2Mb7/axQPbVqMt8nMt7OzbUzImtc3VjGERUcK/pBEhhL6xPbqh5iAnEljVg49PnQs4qABo7u+H6LuYbAm+5cMoYMYuvlEKCWlENle8GlhxaGY8c3JuODh+HXqaJqaC5nt2ThIoe/LjlbdVZvNbsRojYbV2C68rqloKPe4AuBcCCk/U6fgNf1P2d8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_NO = _t, NAME = _t, ROLE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID_NO", Int64.Type}, {"NAME", type text}, {"ROLE", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID_NO", "ROLE"}, {{"Aux", each Text.Combine([NAME],",")}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[ROLE]), "ROLE", "Aux"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"ID_NO", "MANAGER", "ASSISTANT"})
in
    #"Reordered Columns"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.