Can I unpivot multiple non-contiguous rows to columns?

Mark_E

New Member
Joined
Aug 11, 2016
Messages
14
I am trying to import six regional schedules, each with a separate sheet for each week of the month.
When I pull all of the sheets in through power query the dates are scattered on various rows throughout the aggregated editor view.

Is it possible to unpivot all of the rows with dates to columns? I can only get the initial week to do so, and would prefer not to have to do a separate query to each sheet for each region if possible.

The Date is structured like:

74 Rows later:


PopTaskShiftBreakLunchBreak8/5/198/6/198/7/198/8/198/9/19
AdultsWalk In8-4:3012-12:30FilsanAnnMihiretBasmaGary
ADSPhones8-4:3012-12:30MaiRaeAnnDianaRebeccaScott

<tbody>
</tbody>


54 Rows later:


PopTaskShiftBreakLunchBreak8/5/198/6/198/7/198/8/198/9/19
AdultsWalk In8-4:3012-12:30JoanMelindaRachelNaimaRoberta
ADSPhones8-4:3012-12:30CynthiaSamiraJasonRhondaAnn

<tbody>
</tbody>

I want to end with a list of for example, all Adults Walk-Ins grouped by date, but I am not sure of it is possible with the mess I have.

Thanks,
Mark
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Right now I am interested in the processors only, so either would work, but I think I could use the rest of the first example in the future.
That is awesome.
 
Last edited:
Upvote 0
so just filter by processors
the second example it was a joke :) because I really don't know what you want to achieve
 
Last edited:
Upvote 0
check the file from post#11
Now it contain Filter table where you can define one or two or even all Tasks then Refresh green table
if you want remove any Task from the Filter table just delete value or choose blank position from the top of the DataValidation List
Each change requires refreshing the green table
 
Last edited:
Upvote 0
Firefox, Opera:

feature.jpg


Excel Online doesn't support PowerQuery aka Get&Transform (yet!)
 
Upvote 0
When I click open in desktop App nothing happens.
Is it possible for you to share via google drive, drop box, or another alternative to onedrive?
 
Upvote 0
open blank excel workbook first and try again

edit:
after picture from post#16 you can see something like this:

feature2.jpg


choose one :)

of course Allow or Try Again :devilish:
 
Last edited:
Upvote 0
Tried that. Still did not work.
I was able to open the original file from the poster without any difficulty.
 
Upvote 0
because OP used GoogleDrive
I'm not using GD/DropBox because I have the entire archive on OneDrive

Hour of kindness :) , try this

after one hour it will expire!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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