Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ronaldt5
Frequent Visitor

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.

 

ronaldt5_0-1657455865263.png

 

1 ACCEPTED SOLUTION

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"

View solution in original post

6 REPLIES 6
Vijay_A_Verma
Super User
Super User

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. 

 

ronaldt5_0-1657460407449.png

The result should be as follows:

 

ronaldt5_1-1657460603342.png

 

 

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"

Thanks, Vijay.

 

You are spot on. Thanks again.

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.  

 

ronaldt5_0-1657542504010.png

In this case,

ronaldt5_2-1657543312021.png

..... the result should be:

ronaldt5_1-1657543068952.png

 

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"

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors