Forum Discussion

ronaldt5's avatar
ronaldt5
Frequent Visitor
4 years ago
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 conve...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"
ronaldt5's avatar
ronaldt5
Frequent Visitor
4 years ago

Hello Vijay,

 

Additional help is needed.

 

Is it possible to have Critical Component 1, 2 3.. at every change in the PI OCA Ref? See the below screenshot.  

 

In this case,

..... the result should be:

 

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

Use this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfJ1NwSSBUqxOsj8QjR+ERq/GI1fAuY7gflGcPMQ/EI0PsQ8ZzDfGG4egl+Ixseu3gXMN4HYFwsA", 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"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Competency"}, {{"All", each Table.AddIndexColumn(_,"Index",1,1), type table [Competency=nullable text, PI OCA Red=nullable text, Attribute=text, Value=text, Index=Int64.Type]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"PI OCA Red", "Attribute", "Value", "Index"}, {"PI OCA Red", "Attribute", "Value", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All", "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"