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 | 12 |
| audit | afc | jan | 6 |
| audit | afc | feb | 7 |
| audit | afc | mar | 8 |
| audit | afc | apr | 9 |
| audit | afc | may | 10 |
| audit | cnf | jan | 11 |
| audit | cnf | feb | 12 |
| audit | cnf | mar | 13 |
| audit | cnf | apr | 14 |
| audit | cnf | may | 15 |
| audit | rbg | jan | 16 |
| audit | rbg | feb | 17 |
| audit | rbg | mar | 18 |
| audit | rbg | apr | 19 |
| audit | rbg | may | 20 |
| debug | log | jan | 14 |
| debug | log | feb | 15 |
| debug | log | mar | 16 |
| debug | log | apr | 17 |
| debug | log | may | 18 |
| debug | afc | jan | 19 |
| debug | afc | feb | 20 |
| debug | afc | mar | 5 |
| debug | afc | apr | 6 |
| debug | afc | may | 7 |
| debug | cnf | jan | 8 |
| debug | cnf | feb | 9 |
| debug | cnf | mar | 10 |
| debug | cnf | apr | 1 |
| debug | cnf | may | 2 |
| debug | rbg | jan | 3 |
| debug | rbg | feb | 4 |
| debug | rbg | mar | 5 |
| debug | rbg | apr | 5 |
| debug | rbg | may | 6 |
Now, in query editor need to conert sub group values as headers in below format.
| service | attribute | log | afc | cnf | rbg |
| audit | jan | 8 | 6 | 11 | 16 |
| audit | feb | 9 | 7 | 12 | 17 |
| audit | mar | 10 | 8 | 13 | 18 |
| audit | apr | 11 | 9 | 14 | 19 |
| audit | may | 12 | 10 | 15 | 20 |
| debug | jan | 14 | 19 | 8 | 3 |
| debug | feb | 15 | 20 | 9 | 4 |
| debug | mar | 16 | 5 | 10 | 5 |
| debug | apr | 17 | 6 | 1 | 5 |
| debug | may | 18 | 7 | 2 | 6 |
Can any body please help on this ASAP.
Thanks
Praneeth
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
3 Replies
Hi All,
I have data like below and loaded into power bi 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 12 audit log jan 6 audit log feb 7 audit log mar 8 audit log apr 9 audit log may 10 audit cnf jan 11 audit cnf feb 12 audit cnf mar 13 audit cnf apr 14 audit cnf may 15 audit cnf jan 16 audit cnf feb 17 audit cnf mar 18 audit cnf apr 19 audit cnf may 20 debug log jan 14 debug log feb 15 debug log mar 16 debug log apr 17 debug log may 18 debug log jan 19 debug log feb 20 debug log mar 5 debug log apr 6 debug log may 7 debug cnf jan 8 debug cnf feb 9 debug cnf mar 10 debug cnf apr 1 debug cnf may 2 debug cnf jan 3 debug cnf feb 4 debug cnf mar 5 debug cnf apr 5 debug cnf may 6 After laoding data into power bi I want transpose it into below format by using query editor.
service attribute log afc cnf rbg audit jan 8 6 11 16 audit feb 9 7 12 17 audit mar 10 8 13 18 audit apr 11 9 14 19 audit may 12 10 15 20 debug jan 14 19 8 3 debug feb 15 20 9 4 debug mar 16 5 10 5 debug apr 17 6 1 5 debug may 18 7 2 6 Please help me if any have solution for this.
Thanks
Praneeth
Anonymous based on dataset you provided, are you lookig for following result
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
