Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

Thread: Can I unpivot multiple non-contiguous rows to columns?

  1. #1
    New Member
    Join Date
    Aug 2016
    Posts
    14
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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:


    Pop Task Shift Break Lunch Break 8/5/19 8/6/19 8/7/19 8/8/19 8/9/19
    Adults Walk In 8-4:30 12-12:30 Filsan Ann Mihiret Basma Gary
    ADS Phones 8-4:30 12-12:30 Mai RaeAnn Diana Rebecca Scott


    54 Rows later:


    Pop Task Shift Break Lunch Break 8/5/19 8/6/19 8/7/19 8/8/19 8/9/19
    Adults Walk In 8-4:30 12-12:30 Joan Melinda Rachel Naima Roberta
    ADS Phones 8-4:30 12-12:30 Cynthia Samira Jason Rhonda Ann

    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

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,940
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

    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
    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.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    New Member
    Join Date
    Aug 2016
    Posts
    14
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

    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.

    Pop Task 8/6 8/7 8/8 8/9 8/10
    Adults Walkin name1 name2 name3 name4 name5
    ADS phones name6 name7 name8 name9 name10

    54 rows later
    Pop Task 8/13 8/14 8/15 8/16 8/17
    Adults Walkin name11 name12 name13 name14 name15
    ADS phones name16 name17 name18 name19 name20


    74 rows after that:
    Pop Task 8/20 8/21 8/22 8/23 8/24
    Adults Walkin name21 name22 name23 name24 name25
    ADS phones name26 name27 name28 name29 name30

    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

  4. #4
    New Member
    Join Date
    Aug 2016
    Posts
    14
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

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

    Pop Task 8/5 8/6 8/7 8/8 8/9
    Adults Walkins name1 name2 name3 name4 name5
    ADS phones name6 name7 name8 name9 name10




    74 Rows later:


    Pop Task 8/12 8/13 8/14 8/15 8/16
    Adults Walkins name11 name12 name13 name14 name15
    ADS phones name16 name17 name18 name19 name20



    54 Rows later:


    Pop Task 8/17 8/18 8/19 8/20 8/21
    Adults Walkins name21 name22 name23 name24 name25
    ADS phones name26 name27 name28 name29 name30

    65 rows later:

    Pop Task 8/24 8/25 8/26 8/27 8/28
    Adults Walkins name21 name22 name23 name24 name25
    ADS phones name26 name27 name28 name29 name30


    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 by Mark_E; Aug 1st, 2019 at 06:09 PM. Reason: more info

  5. #5
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,940
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

    I don't understand your requirement. Why not list the whole range of data and unpivot it. I seem to be missing something here.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  6. #6
    Board Regular
    Join Date
    Apr 2014
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

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

    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 by bobby_smith; Aug 1st, 2019 at 09:44 PM.

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,853
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Can I unpivot multiple non-contiguous rows to columns?

    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 by sandy666; Aug 2nd, 2019 at 12:27 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  8. #8
    New Member
    Join Date
    Aug 2016
    Posts
    14
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

    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=1Cb...cswIkQIg9KQjhT

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


    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.

  9. #9
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,853
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

    Quote Originally Posted by Mark_E View Post
    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?
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  10. #10
    New Member
    Join Date
    Aug 2016
    Posts
    14
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can I unpivot multiple non-contiguous rows to columns?

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •