Power Query - How to use the values in one field as column headers

[MichaelF1]

Hi, this is what I have:

Year	Country	Measure
2021	GB	16
2021	FR	23
2021	NG	13
2022	GB	9
2022	FR	32
2022	NG	24
2023	GB	25
2023	FR	30
2023	NG	17

This is what I need:

Year	GB Measure	FR Measure	NG Measure
2021	16	23	13
2022	9	32	24
2023	25	30	17

I've tried various combinations of pivot and unpivot but I can't seem to get the right result!

Thanks in advance 🙂

[jpessoa8 · Accepted solution]

Hi @MichaelF1 ,

In this case you will need to use the "Pivot Column" option on the Country Column and selecting as "Values Column" the Column Measure:

This will be the result:

Here is the code of my implementation in PowerQuery:

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

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudo 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Jorge Pessoa