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 List ML1 ML2 ML3 ML4 Feature 1 Release 1   Reease 4 Release 5 Feature 2 Release 2   Reease 5 Release 6 Feature ...
Need Help
Tips and Tricks
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

     , 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

danextian's avatar
danextian
Super User
1 year ago

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