Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to 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"
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"
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.
In this case,
..... the result should be:
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"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!