COnvert rows as column headers

[Anonymous]

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

[Anonymous]

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

[parry2k]

@Anonymous based on dataset you provided, are you lookig for following result

[v-qiuyu-msft · Accepted solution]

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