Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Transpose rows to columns

 

I've tried all the suggestions in the community and not getting desired result. I don't want to summarize the data.

I'm trying to get this...

 

 

from this... where the fields are in one column...any other suggestions?

 

 

 

 

 

 

 

Need Help

5 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    go to the Power Query Editor, add a Custom column

    Number.RoundUp([Index]/4)

    then remove Index column

    then pick field name column, press Pivot Column in Transform ribbon, set parameters as below

    then remove Custom column

     

    as a result you will get your desired table

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi az38 Saz38, sorry for delay to reply.

       

      thank you for the tip on using pivot and customer column. It worked on moving the field names to columns and populated the values on one of the fields (loanid). I think how the data is arranged is a bit off. The values for loanid looks good, however the other values, doctype, batchid, etc. is populating with mostly nulls and incorrect values. It's like when it was pivoting, some of the values got out of order. I'm looking at the data and see if the values are matching up with the fields correctly, and will run it again.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        i think the issue is the field column is not in consistent order..for example loanid, batchid, itemid, doctype is not always in same order. would that cause the results to not populate correctly?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    If the entries in the first column will always be in the order of doctype, docid, batchid and loanid, then i can help.  If that be the case, then paste data in a format that i can take over to MS Excel.

  • rlsmith1994's avatar
    rlsmith1994
    Helper II
    3 years ago

    I think IT folks / programmers are confused on this question. The question is focused on transposing the visualization table, not the data table. If you transpose the data table in the Transform Data window, that will change the underlying structure of the queries.