# Can I unpivot multiple non-contiguous rows to columns?



## Mark_E (Aug 1, 2019)

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/19AdultsWalk In8-4:3012-12:30FilsanAnnMihiretBasmaGaryADSPhones8-4:3012-12:30MaiRaeAnnDianaRebeccaScott

<tbody>

</tbody>
54 Rows later:



PopTaskShiftBreakLunchBreak8/5/198/6/198/7/198/8/198/9/19AdultsWalk In8-4:3012-12:30JoanMelindaRachelNaimaRobertaADSPhones8-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


----------



## alansidman (Aug 1, 2019)

Here is the Mcode using Power Query to unpivot your data.


```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pop", type text}, {"Task", type text}, {"Shift", type text}, {"Break", type any}, {"Lunch", type text}, {"Break2", type any}, {"8/5/2019", type text}, {"8/6/2019", type text}, {"8/7/2019", type text}, {"8/8/2019", type text}, {"8/9/2019", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Pop", "Task", "Shift", "Break", "Lunch", "Break2"}, "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Columns",{"Attribute", "Value", "Pop", "Task", "Shift", "Break", "Lunch", "Break2"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Attribute", Order.Ascending}})
in
    #"Sorted Rows"
```

This is the end result.

Data Range

 *A*​*B*​*C*​*D*​*E*​*F*​*G*​*H*​*1*​Attribute​Value​Pop​Task​Shift​Break​Lunch​Break2​*2*​8/5/2019​Mai​ADS​Phones​8-4:30​​12-12:30​​*3*​8/5/2019​Filsan​Adults​Walk In​8-4:30​​12-12:30​​*4*​8/6/2019​RaeAnn​ADS​Phones​8-4:30​​12-12:30​​*5*​8/6/2019​Ann​Adults​Walk In​8-4:30​​12-12:30​​*6*​8/7/2019​Mihiret​Adults​Walk In​8-4:30​​12-12:30​​*7*​8/7/2019​Diana​ADS​Phones​8-4:30​​12-12:30​​*8*​8/8/2019​Basma​Adults​Walk In​8-4:30​​12-12:30​​*9*​8/8/2019​Rebecca​ADS​Phones​8-4:30​​12-12:30​​*10*​8/9/2019​Scott​ADS​Phones​8-4:30​​12-12:30​​*11*​8/9/2019​Gary​Adults​Walk In​8-4:30​​12-12:30​​

Will this presentation work for you?  If not, please show us a mocked up version of what is acceptable.


----------



## Mark_E (Aug 1, 2019)

The post was partially cutoff, and without the ability to attach an attachment or picture to the post, I unfortunately can't provide a good mock up. 

Let me retry.


PopTask8/68/78/88/98/10AdultsWalkinname1name2name3name4name5ADSphonesname6name7name8name9name10

<tbody>

</tbody>
54 rows later

PopTask8/138/148/158/168/17AdultsWalkinname11name12name13name14name15ADSphonesname16name17name18name19name20

<tbody>

</tbody>

74 rows after that:

PopTask8/208/218/228/238/24AdultsWalkinname21name22name23name24name25ADSphonesname26name27name28name29name30

<tbody>

</tbody>
repeat in 63 rows for the next week, and then again in varying row amounts for each of the 6 regions.  

I can get the unpivot shown, but what I badly asked and didn't show correctly was can I get the other weeks to unpivot too, because whatever I try I just get the first week. 

Thanks,
Mark


----------



## Mark_E (Aug 1, 2019)

I am having a hard time with this editor :/  Apologies for not asking this correctly.


PopTask8/58/68/78/88/9AdultsWalkinsname1name2name3name4name5ADSphonesname6name7name8name9name10

<tbody>

</tbody>



74 Rows later:



PopTask8/128/138/148/158/16AdultsWalkinsname11name12name13name14name15ADSphonesname16name17name18name19name20

<tbody>

</tbody>

54 Rows later:



PopTask8/178/188/198/208/21AdultsWalkinsname21name22name23name24name25ADSphonesname26name27name28name29name30

<tbody>

</tbody>
65 rows later:


PopTask8/248/258/268/278/28AdultsWalkinsname21name22name23name24name25ADSphonesname26name27name28name29name30


<tbody>

</tbody>

repeat at various row counts for each region.  Is it possible to get the other weeks to unpivot as well, because all I can do is get the first week's dates to unpivot. 

That is the type of presentation I am looking for though. I need to get a list of names and tasks by date so I can add an index to match to additional data in a different query. 

Thanks,


----------



## alansidman (Aug 1, 2019)

I don't understand your requirement.  Why not list the whole range of data and unpivot it.  I seem to be missing something here.


----------



## bobby_smith (Aug 1, 2019)

@*Mark_E*

Can you use the insert link feature ( the globe next to the smiley face) to share a picture of what the results should look like in excel?

Should the original data look like this?


Book1ABCDEFG1PopTask6-Aug7-Aug8-Aug9-Aug10-Aug2AdultsWalkinname1name2name3name4name53ADSphonesname6name7name8name9name104567PopTask13-Aug14-Aug15-Aug16-Aug17-Aug8AdultsWalkinname11name12name13name14name159ADSphonesname16name17name18name19name201011121314PopTask20-Aug21-Aug22-Aug23-Aug24-Aug15AdultsWalkinname21name22name23name24name2516ADSphonesname26name27name28name29name30Sheet5


----------



## sandy666 (Aug 2, 2019)

simply post a link to the *shared* (_OneDrive, GoogleDrive, DropBox or any similar_) excel file with representative example of the source data and expected result


----------



## Mark_E (Aug 7, 2019)

Sorry for the delay, I had a family funeral and then have been getting caught back up. 

Here is a link to the file.
https://drive.google.com/open?id=1Cbcc0HkN8HMT30OhhbcswIkQIg9KQjhT

Here is a picture of what I am trying to get to.
https://drive.google.com/open?id=1YG4N4iAyPPzMvS-IBHWCUT5oeJfT4TlM






I can get this output, but only with dates for the first week.  I am trying to get this output for all sheets (each sheet is a week).  As it is, I get all the workers, but the dates are incorrect.


----------



## sandy666 (Aug 7, 2019)

Mark_E said:


> I had a family funeral.


Very sorry.

---
from your picture I can see transform for ADS Processing. It's ok? or post representative (manually created) expected result.
and you want this ADS Processing from all sheets into one table?


----------



## Mark_E (Aug 7, 2019)

She was 94.  It was sad because she was loved, but not tragic.  Thank you for your sympathies.


----------



## Mark_E (Aug 1, 2019)

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/19AdultsWalk In8-4:3012-12:30FilsanAnnMihiretBasmaGaryADSPhones8-4:3012-12:30MaiRaeAnnDianaRebeccaScott

<tbody>

</tbody>
54 Rows later:



PopTaskShiftBreakLunchBreak8/5/198/6/198/7/198/8/198/9/19AdultsWalk In8-4:3012-12:30JoanMelindaRachelNaimaRobertaADSPhones8-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


----------



## sandy666 (Aug 7, 2019)

is that what you want?

example


----------



## Mark_E (Aug 7, 2019)

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 (Aug 7, 2019)

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


----------



## sandy666 (Aug 7, 2019)

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 (Aug 7, 2019)

@*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 (Aug 7, 2019)

Firefox, Opera:







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


----------



## bobby_smith (Aug 7, 2019)

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 (Aug 7, 2019)

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 (Aug 7, 2019)

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


----------



## sandy666 (Aug 7, 2019)

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!


----------



## Mark_E (Aug 1, 2019)

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/19AdultsWalk In8-4:3012-12:30FilsanAnnMihiretBasmaGaryADSPhones8-4:3012-12:30MaiRaeAnnDianaRebeccaScott

<tbody>

</tbody>
54 Rows later:



PopTaskShiftBreakLunchBreak8/5/198/6/198/7/198/8/198/9/19AdultsWalk In8-4:3012-12:30JoanMelindaRachelNaimaRobertaADSPhones8-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


----------



## bobby_smith (Aug 7, 2019)

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 (Aug 7, 2019)

refresh thread and read post#20 again


----------



## bobby_smith (Aug 7, 2019)

I was able to download from post#20.

Thanks for you patience.


----------



## sandy666 (Aug 7, 2019)

Welcome


----------



## Mark_E (Aug 7, 2019)

sandy666 said:


> 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 (Aug 8, 2019)

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 (Aug 8, 2019)

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


----------



## Mark_E (Aug 8, 2019)

sandy666 said:


> 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?



sandy666 said:


> - it will taking time to read and understood


I understand M ok.



sandy666 said:


> - 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. 



sandy666 said:


> - 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.    




sandy666 said:


> 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 (Aug 8, 2019)

Mark_E said:


> 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 (Aug 8, 2019)

As I said, check post#11

Have a nice day


----------



## Mark_E (Aug 1, 2019)

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/19AdultsWalk In8-4:3012-12:30FilsanAnnMihiretBasmaGaryADSPhones8-4:3012-12:30MaiRaeAnnDianaRebeccaScott

<tbody>

</tbody>
54 Rows later:



PopTaskShiftBreakLunchBreak8/5/198/6/198/7/198/8/198/9/19AdultsWalk In8-4:3012-12:30JoanMelindaRachelNaimaRobertaADSPhones8-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


----------



## Mark_E (Aug 8, 2019)

That works great, thanks.


----------



## sandy666 (Aug 8, 2019)

You are welcome


----------

