Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not 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 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_NONAMEROLE
451213A PERSONASSISTANT
451213J SMITHMANAGER
451213JOE ARMASSISTANT
451213FRED FLINTASSISTANT
451213SAM ROCKASSISTANT
775544PAUL WAFMANAGER
775544SIMON FREDASSISTANT
661245KIM TODDMANAGER
787978CHARLIE WILCOXMANAGER
787978CHRIS TOMMANAGER
224455PAUL JONESASSISTANT

 

 

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

ID_NORole1MgerRole2MgerRole1AssistantRole2AssistantRole3AssistantRole4Assistant
451213J SMITH A PERSONJOE ARMFRED FLINTSAM ROCK
775544PAUL WAF SIMON FRED   
661245KIM TODD     
787978CHARLIE WILCOXCHRIS TOM    
224455null 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_NORoleMgrRoleAssistant
451213ASSISTANTA PERSON, JOE ARM, FRED FLINT, SAM ROCK
775544PAUL WAFSIMON FRED
661245KIM TODD 
787978CHARLIE WILCOX, CHRIS TOM 
224455nullPAUL 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 designTable design

 

Any help appciated.

Chris

2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

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"

 

 

SU18_powerbi_badge

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.

 

View solution in original post

Anonymous
Not 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 

 

View solution in original post

3 REPLIES 3
Anonymous
Not 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
Not applicable

Rocco

 

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

 

thanks

 

AlB
Super User
Super User

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"

 

 

SU18_powerbi_badge

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.

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.