Forum Discussion
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 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
, 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"
3 Replies
- bhanu_gautamSuper User
, 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"- gowtham1991Frequent Visitor
bhanu_gautam that as perfect, Thank you verymuch for the support
- danextianSuper User
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