Forum Discussion
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 Headers | Desired New Header |
| AD_TYPE | AD_TYPE |
| AD_WEEK | AD_WEEK |
| AD_YEAR | AD_YEAR |
| PROGRAM_TYPE | PROGRAM_TYPE |
| PROMO_START_DT | PROMO_START_DT |
| PROMO_END_DT | PROMO_END_DT |
| Vendor | Vendor |
| SP_RETAIL_PRICE | SP_RETAIL_PRICE |
| U_PUR_ORG_NUM | U_PUR_ORG_NUM |
| U_PUR_ORG_NM | U_PUR_ORG_NM |
| U_DIST_CHANNEL_NUM | U_DIST_CHANNEL_NUM |
| U_DIST_CHANNEL_NM | U_DIST_CHANNEL_NM |
| B00_QTY | QTY |
| B01_QTY | TRANSMIT_DT |
| B01_TRANSMIT_DT | DELIVERY_DT |
| B01_DELIVERY_DT | PICK_DT |
| B01_PICK_DT | PREQ_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 |
Hi bigdee008
Please check all the applied steps in Query Editor.The last step fill down can be used for other columns you need.
Regards,
Cherie
17 Replies
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?
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 Headers AD_TYPE AD_WEEK AD_YEAR PROGRAM_TYPE PROMO_START_DT PROMO_END_DT Vendor SP_RETAIL_PRICE U_PUR_ORG_NUM U_PUR_ORG_NM U_DIST_CHANNEL_NUM U_DIST_CHANNEL_NM B00_QTY B01_QTY B01_TRANSMIT_DT B01_DELIVERY_DT B01_PICK_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 B08_PICK_DT B08_PREQ_CREATE_DT Desired New Header AD_TYPE AD_WEEK AD_YEAR PROGRAM_TYPE PROMO_START_DT PROMO_END_DT Vendor SP_RETAIL_PRICE U_PUR_ORG_NUM U_PUR_ORG_NM U_DIST_CHANNEL_NUM U_DIST_CHANNEL_NM QTY TRANSMIT_DT DELIVERY_DT PICK_DT PREQ_CREATE_DT SOURCE LDGGROUP_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.
