Help with Table Pivot

Hi Libin7963 ,

Certainly.  I’ve used your original example and pivoted the previously unpivoted table, as shown below:

The following M code will transform the source table above to a pivoted table like below:

let
    // Step 1: Load the source data
    Source = Table.FromRows(
        {
            {1, "A"}, {1, "B"}, {1, "C"}, {1, "D"}, {1, "E"}, {1, "F"}, {1, "G"},
            {2, "H"}, {2, "I"}, {2, "J"}, {2, "K"}, {2, "L"}, {2, "M"}, {2, "N"},
            {3, "O"}, {3, "P"}, {3, "Q"}, {3, "R"}, {3, "S"}, {3, "T"}, {3, "U"},
            {3, "V"}, {3, "W"}
        }, 
        {"Unique Key", "Column1"}
    ),

    // Step 2: Group the data by 'Unique Key'
    GroupedData = Table.Group(
        Source, 
        {"Unique Key"}, 
        {{"AllData", each 
            Table.AddIndexColumn(_, "Rank", 1, 1, Int64.Type)
        }}
    ),

    // Step 3: Convert the Rank number to text with "Column" prefix
    AddPrefix = Table.TransformColumns(
        GroupedData, 
        {
            {"AllData", each 
                Table.TransformColumns(_, {{"Rank", each "Column" & Text.From(_), type text}})
            }
        }
    ),

    // Step 4: Expand the grouped data back to a flat table
    ExpandedTable = Table.ExpandTableColumn(
        AddPrefix, 
        "AllData", 
        { "Column1", "Rank"}
    ),

    // Step 5: Pivot the Rank column
    PivotedTable = Table.Pivot(
        ExpandedTable, 
        List.Distinct(ExpandedTable[Rank]), 
        "Rank", 
        "Column1"
    )
in
    PivotedTable

The resultant table will look like the one below:

You can now create a many-to-one relationship using the Unique Key column.

Best regards,