Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Another Pivot Problem

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 TypeCountryYearValue
AAustralia200832775
AAustralia201026556
AAustralia201221315
BAustralia200821570
BAustralia201022430
BAustralia201225290
ABrazil200835475
ABrazil201034205
ABrazil201231720
BBrazil200834040
BBrazil201021240
BBrazil201235290


Goal

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

Measurement TypeCountry200820102012
AAustralia327752655621315
BAustralia215702243025290
ABrazil354753420531720
BBrazil340402124035290

 

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 TypeCountry
AAustralia
BAustralia
ABrazil
BBrazil

 

What am I missing or doing wrong?

Need Help

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi Anonymous ,

     

    Try this one:

     

    Select the Year column, click on pivot and: 

     

    The point here is not aggregate the values.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • camargos88's avatar
        camargos88
        Community Champion
        6 years ago

        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"