Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
**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 |
Solved! Go to 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.
Regards,
Cherie
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.
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
I went ahead and took a stab at this. Here's the final output of what I did:
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:
Hi @bigdee008
You may add condition column and index column.Then use 'Pivot column'.Attached the sample file for your reference.
Regards,
Cherie
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?
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.
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.
hi @Greg_Deckler the data file can be found here,. I have limited it to 2 rows only but kept all the colums in.
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
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
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |