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?

enter image description here

sonic99
  • 51
  • 3
  • 11
  • 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 Sep 10 '19 at 08:55
  • Does your dates in heading are really dates properly formatted, or it is a text? If second - convert them to dates. – Akina Sep 10 '19 at 09:30
  • @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 Sep 10 '19 at 09:35
  • *There are duplicates as each of the sub sections has eg Sept-19.* Use clear formatting. Subsections must be logical, not physical. – Akina Sep 10 '19 at 09:38
  • @Akina what do you mean there are duplicates? Also, what do you mean by clear formatting? – sonic99 Sep 10 '19 at 09:41
  • *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 Sep 10 '19 at 09:45
  • Possible duplicate of [How to "unpivot" or "reverse pivot" in Excel?](https://superuser.com/questions/78439/how-to-unpivot-or-reverse-pivot-in-excel) – Máté Juhász Sep 10 '19 at 09:46
  • Thank 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 Sep 10 '19 at 10:10
  • 1
    Unpivot 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 Sep 10 '19 at 11:54
  • 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 Sep 10 '19 at 12:10
  • 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 Sep 10 '19 at 12:14

0 Answers0