0
I've been given a data set that consists of hundreds of Stores and almost a hundred dates, below is a small sample. The issue is that dates (mmm-yy) are actually heading names. The reason I see this as an issue is that whenever a new month is made available, the column heading will need to be dragged down to the Values section of the pivot table. Also, I am unable to use a slicer or timeline range to control which months I want to show data for. How can I get around this issue?
sonic99
Posted 2019-09-10T08:42:33.530
Reputation: 51
If the only way to get around this is to unpivot the data, would I be able to keep the current format so that users can continue to use this format, but for this to then be linked to my unpivoted data set? – sonic99 – 2019-09-10T08:55:37.670
Does your dates in heading are really dates properly formatted, or it is a text? If second - convert them to dates. – Akina – 2019-09-10T09:30:08.590
@Akina they are properly formatted, but that also means that they are not unique. There are duplicates as each of the sub sections has eg Sept-19. I look forward to your advice. – sonic99 – 2019-09-10T09:35:45.457
There are duplicates as each of the sub sections has eg Sept-19. Use clear formatting. Subsections must be logical, not physical. – Akina – 2019-09-10T09:38:19.590
@Akina what do you mean there are duplicates? Also, what do you mean by clear formatting? – sonic99 – 2019-09-10T09:41:04.410
what do you mean there are duplicates? I had not pronounced the word "duplicates". That's a citate of YOUR words. what do you mean by clear formatting? The data must be stored (and analyzed) as one solid data array. You may create separate viewings for your sub-sections. Or backward - consolidate your separated data sub-arrays before analyzing. – Akina – 2019-09-10T09:45:03.840
Possible duplicate of How to "unpivot" or "reverse pivot" in Excel?
– Máté Juhász – 2019-09-10T09:46:49.117Thank you @MátéJuhász Is unpivoting the existing data the only way to get around this? I'm afraid the customer won't accept this solution if it means they'll have to use a different format when collecting their data in the future :( – sonic99 – 2019-09-10T10:10:14.210
1Unpivot using Power Query. Use the result of PQ to display the results for analysis. Your customer can still keep the original form for entering the data. PQ should accept the data as being "without headers" so as to maintain the Dates as Dates and not have the resultant table convert them to text. Or else you can make doing that part of the query – Ron Rosenfeld – 2019-09-10T11:54:21.687
Thank you @RonRosenfeld! So my steps are as follows: Import existing data set using PowerQuery. Unpivot the existing data. Let customer use the current data set by adding additional columns as necessary. PowerQuery will automatically unpivot any newly added columns once they've been added. Is this correct? Or am I underestimating the process? – sonic99 – 2019-09-10T12:10:05.793
That sounds close. You may have to, at some point, ensure the "dates" are converted to real dates (depending on what PQ does when you unpivot). And you may also need to edit the PQ M-code to ensure that the new columns are being recognized. But once it is set up, it would just be a matter of refreshing the query when new data is added. – Ron Rosenfeld – 2019-09-10T12:14:02.393
Many thanks @RonRosenfeld – sonic99 – 2019-09-10T12:44:22.453