cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bigdee008
Frequent Visitor

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 looking to shorten the header list and make the repeating headers utilize one column for each of them. i.e. B01 QTY, B02 QTY, B03 QTY, etc would be just  one column called  QTY.  Anyt help would be very much Appreciated.

 

Current HeadersDesired New Header
AD_TYPEAD_TYPE
AD_WEEKAD_WEEK
AD_YEARAD_YEAR
PROGRAM_TYPEPROGRAM_TYPE
PROMO_START_DTPROMO_START_DT
PROMO_END_DTPROMO_END_DT
VendorVendor
SP_RETAIL_PRICESP_RETAIL_PRICE
U_PUR_ORG_NUMU_PUR_ORG_NUM
U_PUR_ORG_NMU_PUR_ORG_NM
U_DIST_CHANNEL_NUMU_DIST_CHANNEL_NUM
U_DIST_CHANNEL_NMU_DIST_CHANNEL_NM
B00_QTYQTY
B01_QTYTRANSMIT_DT
B01_TRANSMIT_DTDELIVERY_DT
B01_DELIVERY_DTPICK_DT
B01_PICK_DTPREQ_CREATE_DT
B01_PREQ_CREATE_DT 
B01_SOURCE 
B01_LDGGROUP_VSR 
B02_QTY 
B02_TRANSMIT_DT 
B02_DELIVERY_DT 
B02_PICK_DT 
B02_PREQ_CREATE_DT 
B03_QTY 
B03_TRANSMIT_DT 
B03_DELIVERY_DT 
B03_PICK_DT 
B03_PREQ_CREATE_DT 
B04_QTY 
B04_TRANSMIT_DT 
B04_DELIVERY_DT 
B04_PICK_DT 
B04_PREQ_CREATE_DT 
B05_QTY 
B05_TRANSMIT_DT 
B05_DELIVERY_DT 
B05_PICK_DT 
B05_PREQ_CREATE_DT 
B06_QTY 
B06_TRANSMIT_DT 
B06_DELIVERY_DT 
B06_PICK_DT 
B06_PREQ_CREATE_DT 
B07_QTY 
B07_TRANSMIT_DT 
B07_DELIVERY_DT 
B07_PICK_DT 
B07_PREQ_CREATE_DT 
B08_QTY 
B08_TRANSMIT_DT 
B08_DELIVERY_DT 

 

 

1 ACCEPTED SOLUTION

Hi @bigdee008

 

Please check all the applied steps in Query Editor.The last step fill down can be used for other columns you need.

1.png1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

17 REPLIES 17
Greg_Deckler
Super User
Super User

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?


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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                                    

 

 

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. 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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 Smiley Sad

 

Anonymous
Not applicable

I went ahead and took a stab at this. Here's the final output of what I did:

Table1.png

 

Not entirely sure if that is what you are looking for ( or maybe more of a step in the correct direction). If I understand you correctly, the next step would be renaming the Current Head with the New Header?

Thanks a lot Nick and Greg, below screengrab shows the row 1 or my actual data set. Maybe that will shed more light on what im trying to accomplish. 

 

I came across the EXACT same thing im trying to accomplish here in the link, however, im not sure how to modify the query utilized there: 

 

Table Headers.png

 

 

 

Hi @bigdee008

 

You may add condition column and index column.Then use 'Pivot column'.Attached the sample file for your reference.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-cherch-msft Thanks for the effort but to say that I  understand what your are suggesting, i would be lying. Again i have to wwarn that I am a complete noob. Can you share a little bit more detail?

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?

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Hi @Greg_Deckler Please see my screenshot belowTable Headers.png

Wow, that is uh, uh, ugly!! I think I figured out what @v-cherch-msft did though. I was able to replicate it in Table24 of attached. Is the file something that you can share? If so, I am fairly certain that I could replicate the procedure. If not, perhaps I could make a video for you that walks you through what was done.

 

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Thanks @Greg_Deckler . I can share the file but Im not sure how to do that on this forum.

If you have confirmed your email address, you can attach to a post. Otherwise, most people just use One Drive, Box, etc.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

hi @Greg_Deckler the data file can be found here,. I have limited it to 2 rows only but kept all the colums in.

 

Dropbox FIle Location

Hi @bigdee008

 

Please check all the applied steps in Query Editor.The last step fill down can be used for other columns you need.

1.png1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thanks a lot @v-cherch-msft I attempted changing the source in the file you shared, however, I am ending up with an error in the Advanced Query Editory everytime. As bizzare as this may sound, i spent the entire day on this yesterday.

Hi @bigdee008

 

You may paste the applied steps to your actual data in query editor.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!