Power Query, un pivot linked data

dbhuff

New member
Joined
Jan 23, 2015
Messages
6
Reaction score
0
Points
0
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?
 
Can you post a workbook and a few rows of required output?
 
Is this the sort of thing you are trying to get to?

OEMLocationProductPlatformRegionTypeYearValue
GMDearbornX2APNorth EastUnits201510000
GMDearbornX2APNorth EastUnits201612000
GMDearbornX2APNorth EastUnits201713000
GMDearbornX2APNorth EastRevenue201510000000
GMDearbornX2APNorth EastRevenue201611500000
GMDearbornX2APNorth EastRevenue201712000000

If so, it was quite straightforward, apart from one where it said Revenue2015 with no spacer. Was that a typo?
 
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. :brushteeth:

PS, lack of space was a typo...
 
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.
 
Back
Top