Forum Discussion
Pivot Table with multiple date columns
I need to create a pivot table with the Material description (Air Con, Motor etc) as the rows and the months/years in the columns starting at Jan-15 to Dec-32 and add a count of number of times that date appears for the machines labelled 4001, 4002 etc.
5 Replies
Dinah15 I believe the attached workbook contains the solution you ask for. It uses Power Query. Firstly, it unpivots you current table, then it cleans and "re-shapes" the data a bit. Loaded into the Data Model (Power Pivot) in order to create "Mth-Yr" from dates, and then create the pivot table as you describe it in Sheet2.
Riny_van_EekelenThankyou that is exactly what I was after. Could you give me a step by step of how to do this myself please?
Dinah15 Have you used Power Query (PQ) before? If not, I suggest you check this out first.
It explains you how to unpivot your table (6 rows, 48 columns) into a list of 3 columns (Material Description, Machine number and Date) and 288 rows (6 X 48).
Now, if are new to PQ, it's not going to be so easy to explain, step-by-step, what needs to be done next. There, aren't many steps involved, but it requires a basic knowledge of PQ. If you have this basic knowledge, you should be able to follow the applied steps in the query provided. If not, it's probably easier to limit the use of PQ to create the unpivoted list (tutorial in the link above) and create a regular pivot table from there, grouping by year and month
BUT!!! You need to resolve an issue with the dates as well. Your current table contains dates like "May-30" as text. You can't just use these texts in a pivot table, expecting them to become dates that can be grouped by year/month. You need to transform them into real dates first. Using the DATEVALUE function doesn't produce the correct result, straight away. In the above example, it would transform "May-30" into May 30, 2020 where you would want it to become May 1, 2030. First, transform the year portion to 4 digits.
=SUBSTITUTE(<cell with date text>,"-","-20")This will create a text "May-2030", which then can be transformed into the desired date by using DATEVALUE. It will produce a number 47604 which can subsequently be custom formatted as a date.
Up to you to decide to if you want to transform your dates before you unpivot the table or if you transform the "dates" afterwards in the unpivoted list before creating a pivot table.
