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
 
Thanks. I've tried everything suggested an its not working.

Can you please share the M code you use so that I can attempt to recreate it?

Thanks.
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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

I am connecting to a sharepoint directory for the query, it isn't "my" data; I need to get the pivotable list to merge with another data set, so I can't add in a filter sheet.
It looks like you have a separate query for each week and then you append them all at the end.
Since I have 6 other substantially similar schedules to bring in, do I just need to spam that query 24 times, one for each sheet involved? I was on a similar track initially, but was hoping there was a better way to do it.
 
Upvote 0
I can do all in a single Query but:
- it will be many lines of the code
- it will taking time to read and understood
- you posted only 4 tables (not proper organised and darkened with colors, which, IMO are unnecessary)
- filter in the sheet was added to make it easier (no problem, you can delete filter in sheet and filter QueryTable and use Filter in PQ Editor)
- you didn't say anything about how you want to resolve this problem so I did all my best (ops, maybe not all :) ). I asked about the expected result and saw nothing
- on the end you didn't say where are the file(s), what the structure is, how you will use it and some other things. PQ is NOT a general solution but tailored to the specific problem

Do you have any idea? then show off
 
Upvote 0
I can do all in a single Query but:
- it will be many lines of the code
Maybe pointing in the general direction, or M for the tricky parts?

- it will taking time to read and understood
I understand M ok.

- you posted only 4 tables (not proper organised and darkened with colors, which, IMO are unnecessary)
This is the data as I get it. Its not my data, I've been asked to re-purpose it. I agree it is ugly and I wouldn't have done it that way.

- on the end you didn't say where are the file(s), what the structure is, how you will use it and some other things. PQ is NOT a general solution but tailored to the specific problem
Once I have the list of workers, tasks, and dates I will add an index. I will take a different set of data (forms in the imaging system) add another index and a new mod column to rotate the index from the workers (number of workers/day determined by a fn) and merge the forms with the workers to generate work assignments.


Do you have any idea? then show off

No, I don't have any ideas. The only way I could get it to work was to do a separate query for each sheet. Every time I tried to do it with multiple sheets imported, I could get all of the workers and their tasks, but only the dates for the first sheet. This will work, but if there is a way to do it in one query, or even one query/regional schedule I would be interested. I wasn't sure it was even possible to do it in one query.
I didn't mean to come across as rude or ungrateful, nothing wrong with coming up with similar solutions.
 
Upvote 0
This will work, but if there is a way to do it in one query, or even one query/regional schedule I would be interested.
Sure, if I'll take more time I'll try. Probably it will be in famous post#11 :)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
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