Forum Discussion
COnvert rows as column headers
Hi all, I have loaded below data into desktop. service SUb group attribute value audit log jan 8 audit log feb 9 audit log mar 10 audit log apr 11 audit log may ...
Hi Anonymous,
You can select the column SUb group then click on the Pivot Column:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZPLDoIwEEV/xXTNQlRe32FcERctFoJRIJWa8PcWZsQW72bS9OQmJ9PbshQvbd5tpUUkzhe1a0xvB3eW42haZcf5/i0fVotrVAppb+3obh594+Zddm7mgNRauVkA8pTGzXgPkBwWFMPUNKNDgGRdrRIpICSRAUISOSDkUMDM9C9edfWqsBEnRA4bcUK8iSNAvIkTTC0aSYCM+j1HnALEGhlArJEDxBoFTM0aB9rGTSvbbFrB8iFijQQg1kgBYo0Mpqaf/Bf5vWD5EJHGRt5vRgIIWaQwM609+xK/GDkg/gcJSfBBQsSLgKHlOQLil+IICCmcAEFL8BuBCBm49Vw/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"service", type text}, {"SUb group", type text}, {"attribute", type text}, {"value", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[#"SUb group"]), "SUb group", "value", List.Sum) in #"Pivoted Column"Best Regards,
Qiuyun Yu
Hi Anonymous,
You can select the column SUb group then click on the Pivot Column:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZPLDoIwEEV/xXTNQlRe32FcERctFoJRIJWa8PcWZsQW72bS9OQmJ9PbshQvbd5tpUUkzhe1a0xvB3eW42haZcf5/i0fVotrVAppb+3obh594+Zddm7mgNRauVkA8pTGzXgPkBwWFMPUNKNDgGRdrRIpICSRAUISOSDkUMDM9C9edfWqsBEnRA4bcUK8iSNAvIkTTC0aSYCM+j1HnALEGhlArJEDxBoFTM0aB9rGTSvbbFrB8iFijQQg1kgBYo0Mpqaf/Bf5vWD5EJHGRt5vRgIIWaQwM609+xK/GDkg/gcJSfBBQsSLgKHlOQLil+IICCmcAEFL8BuBCBm49Vw/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"service", type text}, {"SUb group", type text}, {"attribute", type text}, {"value", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[#"SUb group"]), "SUb group", "value", List.Sum)
in
#"Pivoted Column"
Best Regards,
Qiuyun Yu