excel - Converting unique row data into column data - Stack Overflow

admin管理员组

文章数量:1122846

I've spent hours (days!) researching this including trying to use CoPilot and Gemini without success...Any help would be a much appreciated. Essentially, I need to make the "Item" a unique identifier and the "Part" needs to be listed against each "Item". Each Item may have up to 5 parts, but this could be 6 or more in future.

I want to use Power Query to convert the following table (Table1)

into the output in Table2

If possible, please don't hard-code the lists because I want to be able to scale this up to a file with thousands of Items.

I've spent hours (days!) researching this including trying to use CoPilot and Gemini without success...Any help would be a much appreciated. Essentially, I need to make the "Item" a unique identifier and the "Part" needs to be listed against each "Item". Each Item may have up to 5 parts, but this could be 6 or more in future.

I want to use Power Query to convert the following table (Table1)

into the output in Table2

If possible, please don't hard-code the lists because I want to be able to scale this up to a file with thousands of Items.

• excel
• excel-formula
• powerquery

Share Improve this question Follow edited Nov 26, 2024 at 19:19 Mayukh Bhattacharya 27k88 gold badges2929 silver badges4242 bronze badges asked Nov 22, 2024 at 16:53 KaiKai 3122 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

Using POWER QUERY

---

Paste the following m-code in advanced editor of a blank query, after converting the source range into Structured References (aka Tables)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GroupBy = Table.Group(Source, {"Item"}, {{"All", each _, type table [Part=text, Item=number]}}),
    Custom = Table.AddColumn(GroupBy, "Custom", each Table.AddIndexColumn([All],"Index",1,1)),
    Expand = Table.ExpandTableColumn(Custom, "Custom", {"Part", "Index"}, {"Part", "Index"}),
    AddPrefix = Table.TransformColumns(Expand, {{"Index", each "Part " & Text.From(_, "en-US"), type text}}),
    Remove = Table.RemoveColumns(AddPrefix,{"All"}),
    PivotCol = Table.Pivot(Remove, List.Distinct(Remove[Index]), "Index", "Part")
in
    PivotCol

---

Also, if you are using MS365 then it becomes a classic case of using PIVOTBY()

---

• Formula used in cell D2

=LET(α,B2:B13,PIVOTBY(α,MAP(α,LAMBDA(Σ,"Part "&SUM(N(B2:Σ=Σ)))),A2:A13,SINGLE,,0,,0))

---

本文标签： excelConverting unique row data into column dataStack Overflow