Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
@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 |
Thank you Jennaratten..👍
You're welcome! If this worked for you please click Accept as Solution.
Hello - I have provided two options below. Please let me know if you have any questions.
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