Forum Discussion

bigdee008's avatar
bigdee008
Frequent Visitor
7 years ago
Solved

Unpivot Multiple Columns - Desktop

**total noob alert*    Is there a to unpivot multiple data in PBI desktop?    I have datasource = Excel which is has an unusable data structure. Table headers are shown transposed below: Im looki...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

Invoking ImkeF. Although I'm not 100% clear on this. So, when you transpose your columns (are you?) how many columns of data do you have? Or?

bigdee008's avatar
bigdee008
Frequent Visitor
7 years ago

Thanks for the quick Reply Greg.

 

The file in its current state has 65 Columns.

 

In a desired future state, its should have only about 27 Columns.

 

I transposed the the data in my post to make it more meaningful because the table got stripped out while it got posted.

 

 

The columes are hardly reconizable 

Current HeadersAD_TYPEAD_WEEKAD_YEARPROGRAM_TYPEPROMO_START_DTPROMO_END_DTVendorSP_RETAIL_PRICEU_PUR_ORG_NUMU_PUR_ORG_NMU_DIST_CHANNEL_NUMU_DIST_CHANNEL_NMB00_QTYB01_QTYB01_TRANSMIT_DTB01_DELIVERY_DTB01_PICK_DTB01_PREQ_CREATE_DTB01_SOURCEB01_LDGGROUP_VSRB02_QTYB02_TRANSMIT_DTB02_DELIVERY_DTB02_PICK_DTB02_PREQ_CREATE_DTB03_QTYB03_TRANSMIT_DTB03_DELIVERY_DTB03_PICK_DTB03_PREQ_CREATE_DTB04_QTYB04_TRANSMIT_DTB04_DELIVERY_DTB04_PICK_DTB04_PREQ_CREATE_DTB05_QTYB05_TRANSMIT_DTB05_DELIVERY_DTB05_PICK_DTB05_PREQ_CREATE_DTB06_QTYB06_TRANSMIT_DTB06_DELIVERY_DTB06_PICK_DTB06_PREQ_CREATE_DTB07_QTYB07_TRANSMIT_DTB07_DELIVERY_DTB07_PICK_DTB07_PREQ_CREATE_DTB08_QTYB08_TRANSMIT_DTB08_DELIVERY_DTB08_PICK_DTB08_PREQ_CREATE_DT
Desired New HeaderAD_TYPEAD_WEEKAD_YEARPROGRAM_TYPEPROMO_START_DTPROMO_END_DTVendorSP_RETAIL_PRICEU_PUR_ORG_NUMU_PUR_ORG_NMU_DIST_CHANNEL_NUMU_DIST_CHANNEL_NMQTYTRANSMIT_DTDELIVERY_DTPICK_DTPREQ_CREATE_DTSOURCELDGGROUP_VSR                                    

 

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    OK, well then this really comes down to how you want to combine the columns that you want to combine. So, for example, let's say you have columns A, B and C and you want a single column for them. What are the "rules"?

     

    Because let's say you just want the sum of those in a column, you could create a new column:

     

    D := [A] + [B] + [C]

     

    Then you would delete columns A, B and C in the next step. 

    • bigdee008's avatar
      bigdee008
      Frequent Visitor
      7 years ago

      Thanks Greg.

       

       

      So my situation is,  simply put;

       

      currently, i have Colums A ,B, C, D1, D2, D3, E1, E2, E3 

       

      I want to make the colums A, B, C, D, E. --  .i.e. all transaction data under D1 to D3 will go under D likewise for E1-E3 will go under E without losing their mapping.

       

      Im so sorry If im not being clear enough :smileysad:

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        7 years ago

        Nope, not clear yet, let's try this:

         

        Source data looks like:

         

        A,B,C,D1,D2,D3,E1,E2,E3

        bob,b1,c1,d1,d12,d13,e1,e12,e13

        suzy,b2,c2,d2,d22,d23,e2,e22,e23

         

        And I want it to end up like:

         

        A,B,C,D,E

        bob,b1,c1,d1,e1

        bob,b1,c1,d12,e12

        bob,b1,c1,d13,e13

        suzy,b2,c2,d2,e2

        suzy,b2,c2,d22,e22

        suzy,b2,c2,d23,e23

         

        Is that correct? If not, provide an example, with data for your source and your output, it's the only way to be clear.

         

        bigdee008 - can you provide actual data example like above?