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
AlB
5 years agoCommunity Champion
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. |