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

 

[bobby_smith]

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.

 

[sandy666]

refresh thread and read post#20 again

 

[bobby_smith]

I was able to download from post#20.

Thanks for you patience.

 

[sandy666]

Welcome

 

[Mark_E]

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.

 

[sandy666]

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

 

[sandy666]

Now you can see PivotTable only (file in post#11)

 

[Mark_E]

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.

 

[sandy666]

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

 

[sandy666]

As I said, check post#11

Have a nice day