Excel Problem in Unpivot/Pivot Table In Power Query

Anunay

New Member
Joined
May 4, 2022
Messages
9
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
I need to use power query to bring the initial format to this format that i have mentioned later, i've tried Merging, Transposing, Promoting Headers and Unpivoting Columns but somehow i'm messing up somehwere.

Also need to remove the Random digit values in C column from row 4 to 6.
Here's the source excel

Book4
ABCDEF
1OPM B. No236370236371236372
2Date of booking23-10-2021 00:0024-10-2021 00:0025-10-2021 00:00
3StatusUn PostedPostedPosted
4IngredientsR0010123
5ProductsW0020123
6ByproductsB00326910123
Sheet1






This below here is the final output required.

Book4
ABCDE
1B No.Item Type Item No Date of BookingQty
2236370IngredientsR00123-Oct-211
3236370By-ProductB00323-Oct-211
4236370ProductW00223-Oct-211
5236371IngredientsR00124-Oct-212
6236371By-ProductB00324-Oct-212
7236371ProductW00224-Oct-212
8236372IngredientsR00125-Oct-213
9236372By-ProductB00325-Oct-213
10236372ProductW00225-Oct-213
Sheet2
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Multiple headings requires a few additional steps.
Leila's 13.5 min Youtube video below should help.

 
Upvote 0

Forum statistics

Threads
1,223,881
Messages
6,175,159
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top