Forum Discussion
Converting rows to column
, 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"
, 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:
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"
bhanu_gautam that as perfect, Thank you verymuch for the support