Can I unpivot multiple non-contiguous rows to columns?

[Mark_E]

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:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Pop[/TD]
[TD]Task[/TD]
[TD]Shift[/TD]
[TD]Break[/TD]
[TD]Lunch[/TD]
[TD]Break[/TD]
[TD]8/5/19[/TD]
[TD]8/6/19[/TD]
[TD]8/7/19[/TD]
[TD]8/8/19[/TD]
[TD]8/9/19[/TD]
[/TR]
[TR]
[TD]Adults[/TD]
[TD]Walk In[/TD]
[TD]8-4:30[/TD]
[TD][/TD]
[TD]12-12:30[/TD]
[TD][/TD]
[TD]Filsan[/TD]
[TD]Ann[/TD]
[TD]Mihiret[/TD]
[TD]Basma[/TD]
[TD]Gary[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]Phones[/TD]
[TD]8-4:30[/TD]
[TD][/TD]
[TD]12-12:30[/TD]
[TD][/TD]
[TD]Mai[/TD]
[TD]RaeAnn[/TD]
[TD]Diana[/TD]
[TD]Rebecca[/TD]
[TD]Scott[/TD]
[/TR]
</tbody>[/TABLE]

---

54 Rows later:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Pop[/TD]
[TD]Task[/TD]
[TD]Shift[/TD]
[TD]Break[/TD]
[TD]Lunch[/TD]
[TD]Break[/TD]
[TD]8/5/19[/TD]
[TD]8/6/19[/TD]
[TD]8/7/19[/TD]
[TD]8/8/19[/TD]
[TD]8/9/19[/TD]
[/TR]
[TR]
[TD]Adults[/TD]
[TD]Walk In[/TD]
[TD]8-4:30[/TD]
[TD][/TD]
[TD]12-12:30[/TD]
[TD][/TD]
[TD]Joan[/TD]
[TD]Melinda[/TD]
[TD]Rachel[/TD]
[TD]Naima[/TD]
[TD]Roberta[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]Phones[/TD]
[TD]8-4:30[/TD]
[TD][/TD]
[TD]12-12:30[/TD]
[TD][/TD]
[TD]Cynthia[/TD]
[TD]Samira[/TD]
[TD]Jason[/TD]
[TD]Rhonda[/TD]
[TD]Ann[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[sandy666]

is that what you want?

example

 

[Mark_E]

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.

 

[sandy666]

so just filter by processors
the second example it was a joke because I really don't know what you want to achieve

 

[sandy666]

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

 

[bobby_smith]

@sandy666,

Is your result file still available? I'm unable to open it in any of by browsers (chrome, IE, Opera) neither as an online version nor download it.

I'm interested in seeing the results.

I get this error in IE and Opera https://i.imgur.com/MKHsrL8.png

And this in chrome https://i.imgur.com/khuk8W1.png

 

[sandy666]

Firefox, Opera:

Excel Online doesn't support PowerQuery aka Get&Transform (yet!)

 

[bobby_smith]

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?

 

[sandy666]

open blank excel workbook first and try again

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

choose one

of course Allow or Try Again

 

[bobby_smith]

Tried that. Still did not work.
I was able to open the original file from the poster without any difficulty.

 

[sandy666]

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!