Forum Discussion

gowtham1991's avatar
gowtham1991
Frequent Visitor
1 year ago
Solved

Converting rows to column

hello,

 

I have da data table in the below format

 

Feature ListML1ML2ML3ML4
Feature 1Release 1 Reease 4Release 5
Feature 2Release 2 Reease 5Release 6
Feature 3Release 3 Reease 6Release 7
Feature 4Release 4Release 6Reease 7Release 8
Feature 5Release 5Release 7Reease 8Release 9
Feature 6Release 6Release 8Reease 9Release 10
Feature 7Release 7Release 9Reease 10Release 11
Feature 8Release 8Release 10Reease 11Release 12

 

and the expected outcome should be as beow 

Feature ListRelease 1Release 2Release 3Release 4Release 5
Feature 1Ml1  ML3ML4
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

  •  , 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

3 Replies

  •  , 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

  • 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