Forum Discussion
Groupby Criteria, Power Query Excel
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
HiAnonymous
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.
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
3 Replies
HiAnonymous
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.
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