Forum Discussion
Anonymous
5 years agoNot applicable
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 d...
- 5 years ago
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.
- Anonymous5 years ago
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
Anonymous
5 years agoNot applicable
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
Anonymous
5 years agoNot applicable
Rocco
Thats really good, now discovering pivot columns and Table.Transpose.
thanks