Forum Discussion

Libin7963's avatar
Libin7963
Helper II
1 year ago
Solved

Help with Table Pivot

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

 

Need Help
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    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,

     

     

     

     

4 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    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's avatar
      Libin7963
      Helper II
      1 year ago

      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's avatar
        DataNinja777
        Super User
        1 year ago

        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,