Transformation of Data (Pivot)

[Chris_71720_PBi]

Hello Team

Need you help on how to transform data 

Below is my Data 

AssessmentId	StrategicRiskTitle	VariableTitle	MetricTitle	ScoreValue
4	Access to Capital and Liquidity	X	Impact	2
4	Access to Capital and Liquidity	Y	Likelihood	2
4	Access to Capital and Liquidity	Z	Inherent	4
4	Business Continuity Failure	X	Impact	4
4	Business Continuity Failure	Y	Likelihood	5
4	Business Continuity Failure	Z	Inherent	20
4	Business Continuity Risk	X	Impact	5
4	Business Continuity Risk	Y	Likelihood	6
4	Business Continuity Risk	Z	Inherent	30
4	Compliance Risk	X	Impact	1
4	Compliance Risk	Y	Likelihood	3
4	Compliance Risk	Z	Inherent	3
5	Business Continuity Risk	X	Impact	3
5	Business Continuity Risk	Y	Likelihood	3
5	Business Continuity Risk	Z	Inherent	9
5	Compliance Risk	X	Impact	4
5	Compliance Risk	Y	Likelihood	2
5	Compliance Risk	Z	Inherent	8

And this the out come I need

AssessmentId	StrategicRiskTitle	Impact (X)	Likelihood (Y)	Inherent (Z)
4	Access to Capital and Liquidity	2	2	4
4	Business Continuity Failure	4	5	20
4	Business Continuity Risk	5	6	30
4	Compliance Risk	1	3	3
5	Business Continuity Risk	3	3	9
5	Compliance Risk	4	2	8

[Nathaniel_C · Accepted solution]

Hi @Chris_71720_PBi ,

Here you go!
Steps in Power Query: reorder columns so that you can combine them with the XYZ at the end, next merge the columns with a space and "(", next add a suffix ")" , finally pivot using Score Value, and in the advanced - do not aggregate.

Paste the code below into the Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZI9C8IwEED/SsjsoP0QHbUgCJ2c1NIhtIEebZPaJIP/3kSkQtqm6XCQ4T3ucSTLcIQ3+FQUVAgkOUpIB5I0iLASpfBSUIJ8I43c9VzbjhRSPwKcb7zNh54UatpAxXm51n6avayiPWVmczS4ZyWAGTvhTAJThr8QaFRP7VxfaVQae4pWZLB1ejcQtZ3o3vQzRn17H8uKC/9xCW+7Bggr6GTTzgGOUkIHbBd80XjFdbyMmaQFy2o7Ds7CcSIHOPPjp2Er4IDz/AM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AssessmentId = _t, StrategicRiskTitle = _t, VariableTitle = _t, MetricTitle = _t, ScoreValue = _t]),
    #"Reordered Columns" = Table.ReorderColumns(Source,{"AssessmentId", "StrategicRiskTitle", "MetricTitle", "VariableTitle", "ScoreValue"}),
    #"Merged Columns" = Table.CombineColumns(#"Reordered Columns",{"MetricTitle", "VariableTitle"},Combiner.CombineTextByDelimiter(" (", QuoteStyle.None),"Merged"),
    #"Added Suffix" = Table.TransformColumns(#"Merged Columns", {{"Merged", each _ & ")", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Suffix", List.Distinct(#"Added Suffix"[Merged]), "Merged", "ScoreValue")
in
    #"Pivoted Column"

If you have any questions as to how to do the specific steps, pls let me know.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel