excelexcel-formulapowerquery

Converting unique row data into column data

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)

Table1

into the output in Table2

enter image description here

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.

Solution

  • Using POWER QUERY

    enter image description here

    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()

    enter image description here

    • Formula used in cell D2

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