Forum Discussion

jbowler's avatar
jbowler
Regular Visitor
10 years ago
Solved

transpose

Hi All

 

How do i transpose rows to columns? Is there control to be bespoke about which columns I transpose?

 

I have 5 dimensions in 5 columns, D1 to D5. My 6th dimension (month) is displayed as 1 value per each column (Jan, Feb, Mar etc)

 

I want to transpose the Months so that they move to a new 6th dimension column (Month), and consequently a Values column will be created in the process to store my numbers...

 

How do I do this under the Edit query window? the "Transpose" doesn't allow a parameter to be selected to pick how to transpose. Pivot Column feels closer but it is the reverse that I want to do...essentially pivot a row.

 

does this make sense?

 

thanks

  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    Select your month columns and unpivot? You can thing do a group by or aggregate the resulting columns in DAX measures. Not exactly sure of what you are looking to accomplish eventually, but if you have something like a "1" in one of those Month columns, then you could remove all other rows that do not have a "1" in the resulting unpivot column for value. Something like that might get you there.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    Select your month columns and unpivot? You can thing do a group by or aggregate the resulting columns in DAX measures. Not exactly sure of what you are looking to accomplish eventually, but if you have something like a "1" in one of those Month columns, then you could remove all other rows that do not have a "1" in the resulting unpivot column for value. Something like that might get you there.

    • jbowler's avatar
      jbowler
      Regular Visitor
      10 years ago

      Yes! that's it. :smileyhappy:

       

      Sorry - I couldn't find any user training on how to select and use the 'unpivot'.

       

      Apologies if I missed it (I did search on Transpose) but can I please suggest posting a run through/lesson (with some data examples) on data structures in general, with the implication and the how to for both pivot and unpivot.

       

      thanks for your help