Can I unpivot multiple non-contiguous rows to columns?

M

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:


[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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Here is the Mcode using Power Query to unpivot your data.

Code: 
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
[Table="class: grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][/tr]
[tr][td]
1​
[/td][td]
Attribute​
[/td][td]
Value​
[/td][td]
Pop​
[/td][td]
Task​
[/td][td]
Shift​
[/td][td]
Break​
[/td][td]
Lunch​
[/td][td]
Break2​
[/td][/tr]


[tr][td]
2​
[/td][td]
8/5/2019​
[/td][td]
Mai​
[/td][td]
ADS​
[/td][td]
Phones​
[/td][td]
8-4:30​
[/td][td]
​
[/td][td]
12-12:30​
[/td][td]
​
[/td][/tr]


[tr][td]
3​
[/td][td]
8/5/2019​
[/td][td]
Filsan​
[/td][td]
Adults​
[/td][td]
Walk In​
[/td][td]
8-4:30​
[/td][td]
​
[/td][td]
12-12:30​
[/td][td]
​
[/td][/tr]


[tr][td]
4​
[/td][td]
8/6/2019​
[/td][td]
RaeAnn​
[/td][td]
ADS​
[/td][td]
Phones​
[/td][td]
8-4:30​
[/td][td]
​
[/td][td]
12-12:30​
[/td][td]
​
[/td][/tr]


[tr][td]
5​
[/td][td]
8/6/2019​
[/td][td]
Ann​
[/td][td]
Adults​
[/td][td]
Walk In​
[/td][td]
8-4:30​
[/td][td]
​
[/td][td]
12-12:30​
[/td][td]
​
[/td][/tr]


[tr][td]
6​
[/td][td]
8/7/2019​
[/td][td]
Mihiret​
[/td][td]
Adults​
[/td][td]
Walk In​
[/td][td]
8-4:30​
[/td][td]
​
[/td][td]
12-12:30​
[/td][td]
​
[/td][/tr]


[tr][td]
7​
[/td][td]
8/7/2019​
[/td][td]
Diana​
[/td][td]
ADS​
[/td][td]
Phones​
[/td][td]
8-4:30​
[/td][td]
​
[/td][td]
12-12:30​
[/td][td]
​
[/td][/tr]


[tr][td]
8​
[/td][td]
8/8/2019​
[/td][td]
Basma​
[/td][td]
Adults​
[/td][td]
Walk In​
[/td][td]
8-4:30​
[/td][td]
​
[/td][td]
12-12:30​
[/td][td]
​
[/td][/tr]


[tr][td]
9​
[/td][td]
8/8/2019​
[/td][td]
Rebecca​
[/td][td]
ADS​
[/td][td]
Phones​
[/td][td]
8-4:30​
[/td][td]
​
[/td][td]
12-12:30​
[/td][td]
​
[/td][/tr]


[tr][td]
10​
[/td][td]
8/9/2019​
[/td][td]
Scott​
[/td][td]
ADS​
[/td][td]
Phones​
[/td][td]
8-4:30​
[/td][td]
​
[/td][td]
12-12:30​
[/td][td]
​
[/td][/tr]


[tr][td]
11​
[/td][td]
8/9/2019​
[/td][td]
Gary​
[/td][td]
Adults​
[/td][td]
Walk In​
[/td][td]
8-4:30​
[/td][td]
​
[/td][td]
12-12:30​
[/td][td]
​
[/td][/tr]
[/table]

Will this presentation work for you? If not, please show us a mocked up version of what is acceptable.
 
Upvote 0
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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Pop[/TD]
[TD]Task[/TD]
[TD]8/6[/TD]
[TD]8/7[/TD]
[TD]8/8[/TD]
[TD]8/9[/TD]
[TD]8/10[/TD]
[/TR]
[TR]
[TD]Adults[/TD]
[TD]Walkin[/TD]
[TD]name1[/TD]
[TD]name2[/TD]
[TD]name3[/TD]
[TD]name4[/TD]
[TD]name5[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]phones[/TD]
[TD]name6[/TD]
[TD]name7[/TD]
[TD]name8[/TD]
[TD]name9[/TD]
[TD]name10[/TD]
[/TR]
</tbody>[/TABLE]

54 rows later
[TABLE="width: 500"]
<tbody>[TR]
[TD]Pop[/TD]
[TD]Task[/TD]
[TD]8/13[/TD]
[TD]8/14[/TD]
[TD]8/15[/TD]
[TD]8/16[/TD]
[TD]8/17[/TD]
[/TR]
[TR]
[TD]Adults[/TD]
[TD]Walkin[/TD]
[TD]name11[/TD]
[TD]name12[/TD]
[TD]name13[/TD]
[TD]name14[/TD]
[TD]name15[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]phones[/TD]
[TD]name16[/TD]
[TD]name17[/TD]
[TD]name18[/TD]
[TD]name19[/TD]
[TD]name20[/TD]
[/TR]
</tbody>[/TABLE]


74 rows after that:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Pop[/TD]
[TD]Task[/TD]
[TD]8/20[/TD]
[TD]8/21[/TD]
[TD]8/22[/TD]
[TD]8/23[/TD]
[TD]8/24[/TD]
[/TR]
[TR]
[TD]Adults[/TD]
[TD]Walkin[/TD]
[TD]name21[/TD]
[TD]name22[/TD]
[TD]name23[/TD]
[TD]name24[/TD]
[TD]name25[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]phones[/TD]
[TD]name26[/TD]
[TD]name27[/TD]
[TD]name28[/TD]
[TD]name29[/TD]
[TD]name30[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Upvote 0
I am having a hard time with this editor :/ Apologies for not asking this correctly.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Pop[/TD]
[TD]Task[/TD]
[TD]8/5[/TD]
[TD]8/6[/TD]
[TD]8/7[/TD]
[TD]8/8[/TD]
[TD]8/9[/TD]
[/TR]
[TR]
[TD]Adults[/TD]
[TD]Walkins[/TD]
[TD]name1[/TD]
[TD]name2[/TD]
[TD]name3[/TD]
[TD]name4[/TD]
[TD]name5[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]phones[/TD]
[TD]name6[/TD]
[TD]name7[/TD]
[TD]name8[/TD]
[TD]name9[/TD]
[TD]name10[/TD]
[/TR]
</tbody>[/TABLE]




74 Rows later:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Pop[/TD]
[TD]Task[/TD]
[TD]8/12[/TD]
[TD]8/13[/TD]
[TD]8/14[/TD]
[TD]8/15[/TD]
[TD]8/16[/TD]
[/TR]
[TR]
[TD]Adults[/TD]
[TD]Walkins[/TD]
[TD]name11[/TD]
[TD]name12[/TD]
[TD]name13[/TD]
[TD]name14[/TD]
[TD]name15[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]phones[/TD]
[TD]name16[/TD]
[TD]name17[/TD]
[TD]name18[/TD]
[TD]name19[/TD]
[TD]name20[/TD]
[/TR]
</tbody>[/TABLE]


54 Rows later:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Pop[/TD]
[TD]Task[/TD]
[TD]8/17[/TD]
[TD]8/18[/TD]
[TD]8/19[/TD]
[TD]8/20[/TD]
[TD]8/21[/TD]
[/TR]
[TR]
[TD]Adults[/TD]
[TD]Walkins[/TD]
[TD]name21[/TD]
[TD]name22[/TD]
[TD]name23[/TD]
[TD]name24[/TD]
[TD]name25[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]phones[/TD]
[TD]name26[/TD]
[TD]name27[/TD]
[TD]name28[/TD]
[TD]name29[/TD]
[TD]name30[/TD]
[/TR]
</tbody>[/TABLE]

65 rows later:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Pop[/TD]
[TD]Task[/TD]
[TD]8/24[/TD]
[TD]8/25[/TD]
[TD]8/26[/TD]
[TD]8/27[/TD]
[TD]8/28[/TD]
[/TR]
[TR]
[TD]Adults[/TD]
[TD]Walkins[/TD]
[TD]name21[/TD]
[TD]name22[/TD]
[TD]name23[/TD]
[TD]name24[/TD]
[TD]name25[/TD]
[/TR]
[TR]
[TD]ADS[/TD]
[TD]phones[/TD]
[TD]name26[/TD]
[TD]name27[/TD]
[TD]name28[/TD]
[TD]name29[/TD]
[TD]name30
[/TD]
[/TR]
</tbody>[/TABLE]


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,
 
Last edited:
Upvote 0
I don't understand your requirement. Why not list the whole range of data and unpivot it. I seem to be missing something here.
 
Upvote 0
@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?


Book1
ABCDEFG
1PopTask6-Aug7-Aug8-Aug9-Aug10-Aug
2AdultsWalkinname1name2name3name4name5
3ADSphonesname6name7name8name9name10
4
5
6
7PopTask13-Aug14-Aug15-Aug16-Aug17-Aug
8AdultsWalkinname11name12name13name14name15
9ADSphonesname16name17name18name19name20
10
11
12
13
14PopTask20-Aug21-Aug22-Aug23-Aug24-Aug
15AdultsWalkinname21name22name23name24name25
16ADSphonesname26name27name28name29name30
Sheet5
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
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

open

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.
 
Upvote 0
Mark_E said:
I had a family funeral.
Click to expand...
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Help win unpivot super slow query
2
Replies
15
Views
1K
labello27
L
J
  • Question Question
Excel Power Query & Power Pivot: Ageing Trend
Replies
1
Views
288
jajatidev
J
A
  • Solved
Power Query to arrange 8 Different Headers as two different columns
Replies
5
Views
298
ashish128
A
J
  • Solved
Missy Record from Power Query
Replies
4
Views
470
jarett
J
A
  • Question Question
Excel Problem in Unpivot/Pivot Table In Power Query
Replies
1
Views
770
Alex Blakenburg
A

Forum statistics

Threads
1,223,099
Messages
6,170,111
Members
452,302
Latest member
TaMere

Share this page

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