let
Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PMA PART NUMBER", type text}, {"DESCRIPTION", type text}, {"BULLETIN# & KIT#", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"BULLETIN# & KIT#"}, {{"Data", each _, type table [PMA PART NUMBER=nullable text, DESCRIPTION=nullable text, #"BULLETIN# & KIT#"=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"PMA PART NUMBER", "DESCRIPTION", "Index"}, {"Custom.PMA PART NUMBER", "Custom.DESCRIPTION", "Custom.Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Data"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "BULLETIN# & KIT#")
in
#"Pivoted Column"
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "
Bookmarks