Pivoting Issue.

[Jraj2380]

@Getting count while doing Pivot of below table, need to Sum of Values against each Executive and Attributes as column headers. Thank you.

Executive	Attribute	Value
JAI	OD	234242
JAI	OS	343243
GIRI	OD	324232
GIRI	OS	543533

[Jraj2380]

Thank you Jennaratten..👍

[jennratten]

You're welcome!  If this worked for you please click Accept as Solution.

[jennratten]

Hello - I have provided two options below. Please let me know if you have any questions.

1. The attribute is the column headers with the executive as the row label and the sum as the value. 
2. The column headers as executive + attribute with no row label.  

Option 1 steps:

Option 1 Result

Option 1 Script

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nL0VNJR8ncBEkbGJkYmRkqxOnDRYCBhbGJsZGIMFnX3DIIrBgoaGRuhCINUm5oYmxoDVccCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Executive = _t, Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"

Option 2 Script for exec + attribute as headers.  It is just like option 1 but you have to add a combined column first and then pivot on that.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nL0VNJR8ncBEkbGJkYmRkqxOnDRYCBhbGJsZGIMFnX3DIIrBgoaGRuhCINUm5oYmxoDVccCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Executive = _t, Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Temp", each [Executive] & "_" & [Attribute]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Executive", "Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Temp]), "Temp", "Value", List.Sum)
in
    #"Pivoted Column"

Option 2 result