combine multiple date columns with value into single date and value columns

Question

I'm struggling to wrangle my data

I have a selection of data that:

• Has multiple columns, each with a specific date as a header
• Has value in each date column cells
• I have row headers, representing product type
• I have row sub-headers, representing territory

I would like:

• A single column with the date
• A single column with the related values, per date
• A single column with the product type
• A single Column with Territory

Is this possible?

Below is an example of what the raw data would look like:

What I am trying to achieve:

I hope there is an excel wizard who can help with this query.

Joe

Answer 1

Joe,

Difficult to say with the picture you posted and I understand you cannot share sensitive data, although you could easily put together a sample with dummy data, that would not take more than 10 mins, keep this is mind in the following doesn't help

1st of all, you've probably observed I rename almost all APPLIED STEPS created with the UI. Simple reason is I hate reading i.e. #"Grouped Rows"

So you can follow I don't do that below:

#1 Once you've referenced your data Table to build your Result query, in PQ you see something like

#2 Select columns [Product Type] and [Territory] > Go to Transform tab > click the little arrow down icon next to Unpivot Columns > choose Unpivot Other Columns. You should get:

you see that by default unpivoted columns are named [Attribute] and [Value]. That's where I "cleverly" made a modif. At the end we want a [Date] column. So we could continue the default [Attribute] column name but then we would need an additional Step at the end to rename that column => [Date]. To prevent this, just edit the Step in the formula bar and change the string "Attribute" with… "Date" :

#3 Select  column [Date] > in the Transform tab, next to Data Type, select Date in the list:

#4 Select columns [Product Type], [Territory] and [Date] > Right-click > Group By… use the following:

OK ==> Done!!!

Answer 2

Hi Lz,

the data set I provided was an example as the data I would like to apply this to is a little more sensitive. 

I have had a look at your power pivot and, while a lot of it I can replicate, the UnpivotedDateAndValue step is a mystery.

Any chance you could elaborate this step?

  

Also,

Wizard of Oz