Grouping By Name?

Craigc3814

Board Regular
Joined
Mar 7, 2016
Messages
217
I thought grouping would solve my issue but it does not, I have pivoted, and unpivoted this data so many times just to get to this point I am pretty let down.

In column 1 I have a header called EE Name which is just an employees name. Column 2 is called Attribute and it us just a 2 digit code for a certification that they have.

What I want to do is eliminate the duplicates in the EE Name column and make that the column header, then each unique attribute they have I want a row for that. So

Ken Puls A1
Ken Puls A2
Ken Puls A4
Miguel Escobar A1
Miguel Escobar A2

I want to turn into

Ken Puls Miguel Escobar
A1 A1
A2 A2
A3
A4
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
I think I got it figured out, I grouped by name, then selected operation as all rows. I then transposed that, made the first row headers and its giving me the option to expand each table with all attribute. I can one by one expand them. If I could quickly do them all that would be great, but this will work
 
Upvote 0
Watch this video to see how the 2 solutions below are created.

1. Pivot: for that you need a copy of your Attribute column; the result has a row for each distinct attribute, so lots of nulls.

Code:
let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\MrExcel Power BI\Grouping by name.xlsx"), null, true),
    Tabel1_Table = Source{[Item="Tabel1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Tabel1_Table,{{"EE Name", type text}, {"Attribute", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Attribute", "Attribute - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"EE Name"]), "EE Name", "Attribute"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute - Copy"})
in
    #"Removed Columns"

2. Group By: all values with some additional steps. No nulls, but some hardcoded fieldnames: I take a closer look later today.

Code:
let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\MrExcel Power BI\Grouping by name.xlsx"), null, true),
    Tabel1_Table = Source{[Item="Tabel1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Tabel1_Table,{{"EE Name", type text}, {"Attribute", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"EE Name"}, {{"Attributes", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Transpose([Attributes]){1}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attributes"}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}),
    #"Transposed Table" = Table.Transpose(#"Expanded Custom"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Ken Puls", type text}, {"Miguel Escobar", type text}, {"MarcelBeug", type text}})
in
    #"Changed Type1"
 
Last edited:
Upvote 0
New video and adjusted code to avoid hard coded column names when expanding the record column and changing the data type in the last step (alternatively this step can be omitted all together).
At the end of the video it is illustrated that the code still works fine when a name is added.

Code:
let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\MrExcel Power BI\Grouping by name.xlsx"), null, true),
    Tabel1_Table = Source{[Item="Tabel1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Tabel1_Table,{{"EE Name", type text}, {"Attribute", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"EE Name"}, {{"Count", each Table.RowCount(_), type number}, {"AllData", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Transpose([AllData]){1}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllData"}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", List.Transform({1..List.Max(#"Removed Columns"[Count])},each "Column"&Text.From(_))),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers", List.Transform(Table.ColumnNames(#"Promoted Headers"), each {_, type text}))
in
    #"Changed Type1"
 
Upvote 0
Thank you!!! I will be reviewing this ASAP. I ended up going to different route for now. I just made pivot tables of each option that I needed met, and it worked (not as easy on the eyes as I would like). But will use what you have posted the next time this issue comes up.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top