Results 1 to 6 of 6

Thread: Unpivot date data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,984
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Unpivot date data

    Can someone please help me? My data in a huge XLSM file looks like this, with a column for the year, a column for the month and a row containing the day all the way rightways to 31 (so ignore that it's in Column H - I didn't want to post 33 columns here).

    A B C D E F G H
    1 Year Month 1 2 3 4 5 ... 31
    2 1900 1 -14.4 -11.7 -12.2 -12.2 -6.7 4.4
    3 1900 2 4.5 -2.2 -7.8 -12.2 2.8 -4.4
    4 1900 3 6.1 -2.2 -14.4 -16.7 -17.2 -8.9
    5 1900 4 18.9 18.9 17.8 17.8 17.8 21.7
    6 1900 5 22.8 18.9 26.1 24.4 23.3 18.9
    7 1900 6 18.3 22.2 22.8 23.9 22.2 18.3
    8 1900 7 15 15.6 16.1 21.1 20 15.6
    max



    I wish to use Power Query to transform the data to this, with a column for the date and a column for the value. But I don't know how - can someone walk me through it please?

    A B
    1 Date value
    2 1/1/1900 -14.4
    3 1/2/1900 -11.7
    4 1/3/1900 -12.2
    5 1/4/1900 -12.2
    6 1/5/1900 -6.7
    7
    8 1/31/1900 7.2
    9 2/1/1900 4.5
    Sheet4
    Windows10, Excel 365 Insider
    If you don't declare what version of Excel, we will assume
    Windows10, Excel 365.
    Formulas here are always in
    Green background
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

  2. #2
    Board Regular Useful's Avatar
    Join Date
    Mar 2011
    Location
    Azerbaijan, Baku
    Posts
    494
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unpivot date data

    Hello,

    select your table PQ/Excel Data/From Table - > select first two columns (Years and Month) and from Transform/Unpivot other columns

    (and here "Attribute" columns would be your dates)
    My Excel World One World One Dream!
    Always search for the easiest solution!
    "MyExcelWorld" Excel Add-in
    Follow me on YouTube
    FacebookPage

    Webmoney: Z142166028079, E615175446011, R848776396434

    Microsoft MVP - Excel

    Best,
    Aydin Aliyev

  3. #3
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unpivot date data

    Quote Originally Posted by Useful View Post
    Hello,

    select your table PQ/Excel Data/From Table - > select first two columns (Years and Month) and from Transform/Unpivot other columns

    (and here "Attribute" columns would be your dates)

    1. Select columns with day number and then select unpivot (not as suggested above!)
    2. Change "Attribute" to Data Type Whole Number
    3. Add custom column with Formula #date([Year],[Month],[Attribute]))
    4. Remove not needed columns and reorganize view as you wish

  4. #4
    Board Regular Useful's Avatar
    Join Date
    Mar 2011
    Location
    Azerbaijan, Baku
    Posts
    494
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unpivot date data

    Quote Originally Posted by ralliartur View Post
    1. Select columns with day number and then select unpivot (not as suggested above!)
    ralliartur be careful about unpivot step (suggested "Unpivot other columns" but not "Unpivot Columns" ). Instead of selecting 31 columns of the days it'll better to select Year and Month columns and Unpivot other columns.
    My Excel World One World One Dream!
    Always search for the easiest solution!
    "MyExcelWorld" Excel Add-in
    Follow me on YouTube
    FacebookPage

    Webmoney: Z142166028079, E615175446011, R848776396434

    Microsoft MVP - Excel

    Best,
    Aydin Aliyev

  5. #5
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unpivot date data

    I was suggesting to use default action - keep in mind that it is not always obvious to use dropdown list in ribbon menu.

  6. #6
    Board Regular Useful's Avatar
    Join Date
    Mar 2011
    Location
    Azerbaijan, Baku
    Posts
    494
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unpivot date data

    i suggest you to read careful the next time before posting
    My Excel World One World One Dream!
    Always search for the easiest solution!
    "MyExcelWorld" Excel Add-in
    Follow me on YouTube
    FacebookPage

    Webmoney: Z142166028079, E615175446011, R848776396434

    Microsoft MVP - Excel

    Best,
    Aydin Aliyev

Some videos you may like

User Tag List

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
  •