Help with Table Pivot

[Libin7963]

My table looks as shown below on the left and I want it to look as the one on the right, please assist

[DataNinja777]

Hi @Libin7963 ,

You can achieve your required output in multiple different ways, and one of them is as follows.

First, write a calculate column DAX like below:

Rank = "Column"&
RANKX(
    FILTER(
        Data, 
        Data[Unique Key] = EARLIER(Data[Unique Key])
    ), 
    Data[Index], 
    , 
    ASC, 
    DENSE
)

Then write a ConcatenateX measure to put in the values section of the matrix table.

ConcatenateX Column1 = concatenatex('Data','Data'[Column1])

The resulting output is as shown below:

Since there is no inherent row order in Power BI's data view, it is important to insert an index column using Power Query at the beginning.

I have attached an example pbix file for your reference.

Best regards,

[Libin7963]

Thanks for your reply, Sorry if I was not clear enough. Can it be done at power query level and not in visual. I want to build a many to one relationship to this table's unique key once it is made unique. Relationship is between these two Primary key columns as One to many.

Here is a better example scenario.

Table 1

Table 2

Report view

[DataNinja777 · Accepted solution]

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,

[Libin7963]

Thank you, that worked...