Forum Discussion
Solved
Unpivot Column in Power Query
Hello.
I would like to have the "critical component" items transferred to separate columns and have one single row as a result. Please help with a DAX formula that identifies duplicates and converts the respective critical components to separate columns and thereafter removes duplicates so that I get one row.
Use this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfJ1NwSSBUqxOsj8QjR+ERq/GI1fohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Competency = _t, #"PI OCA Red" = _t, #"Critical Components" = _t]), #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Competency", "PI OCA Red"}, "Attribute", "Value"), #"Added Index" = Table.AddIndexColumn(#"Unpivoted Columns", "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [Attribute]&" "&Text.From([Index])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute", "Index"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value") in #"Pivoted Column"
6 Replies
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
Try both the codes and see which one is wanted by you.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfJ1NwSSBUqxOsj8QjR+ERq/GI1fohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Competency = _t, #"PI OCA Red" = _t, #"Critical Components" = _t]), #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[#"Critical Components"]), "Critical Components", "PI OCA Red") in #"Pivoted Column"let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfJ1NwSSBUqxOsj8QjR+ERq/GI1fohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Competency = _t, #"PI OCA Red" = _t, #"Critical Components" = _t]), #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[#"Critical Components"]), "Critical Components", "Competency") in #"Pivoted Column"Hello Vijay,
Unfortunately, this is not what i get.
The result should be as follows:
Use this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfJ1NwSSBUqxOsj8QjR+ERq/GI1fohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Competency = _t, #"PI OCA Red" = _t, #"Critical Components" = _t]), #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Competency", "PI OCA Red"}, "Attribute", "Value"), #"Added Index" = Table.AddIndexColumn(#"Unpivoted Columns", "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [Attribute]&" "&Text.From([Index])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute", "Index"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value") in #"Pivoted Column"
