Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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:
Any help appciated.
Chris
Solved! Go to Solution.
Hi@Anonymous
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
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
Rocco
Thats really good, now discovering pivot columns and Table.Transpose.
thanks
Hi@Anonymous
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. |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
60 | |
54 | |
27 | |
16 | |
9 |