Converting rows to column

[gowtham1991]

hello,

I have da data table in the below format

Feature List	ML1	ML2	ML3	ML4
Feature 1	Release 1		Reease 4	Release 5
Feature 2	Release 2		Reease 5	Release 6
Feature 3	Release 3		Reease 6	Release 7
Feature 4	Release 4	Release 6	Reease 7	Release 8
Feature 5	Release 5	Release 7	Reease 8	Release 9
Feature 6	Release 6	Release 8	Reease 9	Release 10
Feature 7	Release 7	Release 9	Reease 10	Release 11
Feature 8	Release 8	Release 10	Reease 11	Release 12

and the expected outcome should be as beow 

Feature List	Release 1	Release 2	Release 3	Release 4	Release 5
Feature 1	Ml1			ML3	ML4
Feature 2		ML2
Feature 3			ML1
Feature 4				ML1
Feature 5
Feature 6
Feature 7
Feature 8

I am bit struggling to convert the table in specified format, any suggestions and ideas would be helpful

Thanks

Gowtham

[danextian]

Hi @gowtham1991 

Please try this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddC7DYAwDATQVZDrFCSQDwswAG2UgsIdFZ/9KShyh+TuLOvpLNcqq+73c+rgxcmmh+7Xl2GcIUdprqMAm8AoQk6EJthMjBLkTGg27mHRcyEdjcNY9LyQTkYfC3jfSDwbhT8CgydfjMq/gSFIay8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Feature List" = _t, ML1 = _t, ML2 = _t, ML3 = _t, ML4 = _t]),
    
    // Unpivot all columns except 'Feature List' so ML values become rows
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Feature List"}, "Attribute", "Value"),

    // Remove rows where the 'Value' column is null or empty
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each [Value] <> null and [Value] <> ""),

    // Create a new column 'Value2' by extracting the release number from 'Value'
    // This ensures a consistent "Release X" naming format
    #"Added Custom" = Table.AddColumn(
        #"Filtered Rows", 
        "Value2", 
        each "Release " & Text.PadStart(Text.AfterDelimiter([Value], " "), 2, "0"), 
        type text
    ),

    // Remove the original 'Value' column, keeping only 'Value2'
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),

    // Rename 'Value2' back to 'Value' to maintain expected column names
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value2", "Value"}}),

    // Pivot the table so that 'Value' becomes column headers, and ML attributes are placed under them
    #"Pivoted Column" = Table.Pivot(
        #"Renamed Columns", 
        List.Distinct(List.Sort(#"Renamed Columns"[Value])), 
        "Value", 
        "Attribute"
    ),

    // Dynamically rename the pivoted columns to maintain proper "Release X" formatting
    Custom1 = 
        let 
            tbl = #"Pivoted Column",
            ColumnNames = List.Skip(Table.ColumnNames(tbl), 1), // Skip the first column ('Feature List')
            RenameTo = List.Transform(
                ColumnNames, 
                each "Release " & (try Text.From(Number.From(Text.AfterDelimiter(_, " "))) otherwise null)
            )
        in 
        Table.RenameColumns(tbl, List.Zip({ColumnNames, RenameTo}))
in
    Custom1

[bhanu_gautam · Accepted solution]

 , Use Unpivot in Power Query 

Once your data is loaded, click on "Transform Data" to open the Power Query Editor.

In the Power Query Editor, select the columns ML1, ML2, ML3, and ML4.
Right-click on the selected columns and choose "Unpivot Columns". This will transform your data into a long format with columns Feature List, Attribute, and Value.
Filter out empty values:

Filter out rows where the Value column is empty.
Pivot the data:

Select the Value column.
Go to the "Transform" tab and click on "Pivot Column".
In the Pivot Column dialog, set the Attribute column as the values column and choose an appropriate aggregation function (e.g., "Don't Aggregate").

Rename the columns as needed to match your desired output.
Remove any unnecessary columns.

Here is a step-by-step example using Power Query M code:

m
let
Source = Excel.Workbook(File.Contents("C:\path\to\your\file.xlsx"), null, true),
Sheet1 = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1, [PromoteAllScalars=true]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Feature List"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> null)),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Value]), "Value", "Attribute")
in
#"Pivoted Column"

@gowtham1991

[gowtham1991]

@bhanu_gautam  that as perfect, Thank you verymuch for the support