Another Pivot Problem

[Anonymous]

Update: It appears that there is a row limit for pivot tables.  If I reduce the number of rows from 4,000 to 2,000, then it works. 
I will have to filter the table before I can pivot it.
Thank you for the responses, but it looks like I can't delete this post, unfortunately.

---  

Original message:

Suppose I have a table like this:

Measurement Type	Country	Year	Value
A	Australia	2008	32775
A	Australia	2010	26556
A	Australia	2012	21315
B	Australia	2008	21570
B	Australia	2010	22430
B	Australia	2012	25290
A	Brazil	2008	35475
A	Brazil	2010	34205
A	Brazil	2012	31720
B	Brazil	2008	34040
B	Brazil	2010	21240
B	Brazil	2012	35290

Goal

I want to pivot the Year column so that the data is shaped like this:

Measurement Type	Country	2008	2010	2012
A	Australia	32775	26556	21315
B	Australia	21570	22430	25290
A	Brazil	35475	34205	31720
B	Brazil	34040	21240	35290

What I'm seeing instead

When I attempt to pivot the Year column, using the values in the Value column, the result is a table with only the first two columns: the Year and Values are gone.  I've tried using the "Advanced options" set to "Don't aggregate" and set to "Average", and both options result in this:

Measurement Type	Country
A	Australia
B	Australia
A	Brazil
B	Brazil

What am I missing or doing wrong?

[Anonymous]

HI @Anonymous,

I'd like to suggest you to use the import feature to import excel workbook and its expression formulas:

Import Excel workbooks into Power BI Desktop 
Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , see if this help

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[Anonymous]

If I transpose the table I get every country as it's own column, which is... different, unfortunately.

[camargos88]

@Anonymous ,

Paste this code on the Advanced Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY+xDoQwDEP/pTND4iSUG+E3EENHJCYOFr7+aCtBpWsnR7L17MyzG13nxvN77GFbw32DaLhF4L25pasFmKL0Zn0rgCgsnAlTvQJsnhqBXAGVZiBVGD70bJj2cK1b8YJp8ULhJrgoqO5GsrDHW/1HVtKqm2czGm4i583LDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Measurement Type" = _t, Country = _t, Year = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Measurement Type", type text}, {"Country", type text}, {"Year", Int64.Type}, {"Value", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "pt-BR"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "pt-BR")[Year]), "Year", "Value")
in
#"Pivoted Column"

[camargos88]

Hi @Anonymous ,

Try this one:

Select the Year column, click on pivot and: 

The point here is not aggregate the values.

[Anonymous]

Indeed, that was what I did, but the outcome was not as expected, however I appreciate the message.

[lbendlin]

Measure Type and Country go into the Row bucket, Year goes into the Column bucket, and Value to the values.  Is that what you did?

[Anonymous]

If I was using the matrix vizualization, that would give the desired appearance, but alas, it would not actually change the table.