Results 1 to 7 of 7

Thread: Power Query, un pivot linked data

  1. #1

    Power Query, un pivot linked data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Unpivot data is fantastic to set up data for subsequent pivot table usage. However, I have a situation I can't determine. I have n columns marked Units <date> and n more columns marked Value <date>. Since the units on <date> go with value <date>, just unpivoting the columns leads to too many entries, which I have to delete, for instance units on date1 and value on date 2. Ultimately I need n rows per line not n^2. Does anyone have any ideas how to do that?

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,823
    Articles
    0
    Excel Version
    O365
    Can you post a workbook and a few rows of required output?

  3. #3
    Thanks Bob, I've attached a file with the general structure I'm talking about. I need to unpivot both units and revenue columns, but the years should be the same...Example for de-pivot.xlsx

    Thanks for any suggestions.

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,823
    Articles
    0
    Excel Version
    O365
    Is this the sort of thing you are trying to get to?

    OEM Location Product Platform Region Type Year Value
    GM Dearborn X2 AP North East Units 2015 10000
    GM Dearborn X2 AP North East Units 2016 12000
    GM Dearborn X2 AP North East Units 2017 13000
    GM Dearborn X2 AP North East Revenue 2015 10000000
    GM Dearborn X2 AP North East Revenue 2016 11500000
    GM Dearborn X2 AP North East Revenue 2017 12000000

    If so, it was quite straightforward, apart from one where it said Revenue2015 with no spacer. Was that a typo?

  5. #5
    Excel 2010 with free Power Query Add-In.
    Compatible with Office 2013 Pro Plus.
    With Merge, Split and Unpivot Collumns.
    Same as Bob's.
    Quite easy.
    http://www.mediafire.com/view/ysn1zu...01_26_15a.xlsx

  6. #6
    Herb great, just what I needed (and way above and beyond on the demo!)

    Bob, close, but I wanted 2015 rows to have both revenue and unit counts. Its the merge and split functionality I needed to use, thanks again! In fact, you've cleaned up the result nicely too.

    PS, lack of space was a typo...

  7. #7
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,823
    Articles
    0
    Excel Version
    O365
    With great respect, I think having Units and Amount as separate columns when you are then going to pivot it is dumb . De-normalise it fully, the pivot can handle the layout.

Posting Permissions

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