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 Joe

The wizard is called Get & Transform (aka Power Query)

Data in Table1

Query output

Could you play with the corresponding sample (download it) to check this does what you want, I'll explain later (no worries, very easy)

Any question let me know

Answer 3

Hi Lz,

this is amazing.

When you have a chance, please show me the magic behind the curtain (wizard of Oz reference).

Joe

Answer 4

please show me the magic behind the curtain (wizard of Oz reference). ;-)

Hi Joe

Can you live with the sample I provided or do you want me to explain How To reproduce it on your end?

If you can live with it then please Mark as answer my previous reply (can help others…) - Thank in advance

Otherwise I will explain but it'll take me some time to doc. Again, it's not difficult but there's one thing that is a little bit tricky as we don't necessarily know (in your case) how many Date columns we have