Transform data

[esbencito]

Hi all,

I need to transform/ re-arrange data in Access. Below table illustrates the data that I currently have:

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Year[/TD]
[TD]FY18[/TD]
[TD]FY18[/TD]
[TD]FY18[/TD]
[TD]FY18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Month[/TD]
[TD]MAR[/TD]
[TD]MAR[/TD]
[TD]FEB[/TD]
[TD]FEB[/TD]
[/TR]
[TR]
[TD]Country[/TD]
[TD]Channel[/TD]
[TD]Brand[/TD]
[TD]Item Number[/TD]
[TD]SLS UNT[/TD]
[TD]SLS CST[/TD]
[TD]SLS UNT[/TD]
[TD]SLS CST[/TD]
[/TR]
[TR]
[TD]China[/TD]
[TD]RLS[/TD]
[TD]MSW[/TD]
[TD]10004[/TD]
[TD]34[/TD]
[TD]$87.00[/TD]
[TD]56[/TD]
[TD]$80.00[/TD]
[/TR]
[TR]
[TD]Vietnam[/TD]
[TD]RLE[/TD]
[TD]MSW[/TD]
[TD]10006[/TD]
[TD]32[/TD]
[TD]$76.00[/TD]
[TD]45[/TD]
[TD]$92.00[/TD]
[/TR]
[TR]
[TD]China[/TD]
[TD]RLC[/TD]
[TD]WSW[/TD]
[TD]10009[/TD]
[TD]29[/TD]
[TD]$97.00[/TD]
[TD]47[/TD]
[TD]$71.00[/TD]
[/TR]
[TR]
[TD]Thailand[/TD]
[TD]RLS[/TD]
[TD]WSW[/TD]
[TD]10008[/TD]
[TD]17[/TD]
[TD]$65.00[/TD]
[TD]50[/TD]
[TD]$83.00[/TD]
[/TR]
[TR]
[TD]Singapore[/TD]
[TD]RLC[/TD]
[TD]CSW[/TD]
[TD]10003[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]38[/TD]
[TD]$91.00[/TD]
[/TR]
</tbody>[/TABLE]

In order to pivot it, I want it to look like this:

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Month[/TD]
[TD]Country[/TD]
[TD]Channel[/TD]
[TD]Brand[/TD]
[TD]Item Number[/TD]
[TD]SLS UNT[/TD]
[TD]SLS CST[/TD]
[/TR]
[TR]
[TD]FY18[/TD]
[TD]MAR[/TD]
[TD]China[/TD]
[TD]RLS[/TD]
[TD]MSW[/TD]
[TD]10004[/TD]
[TD]34[/TD]
[TD]$87.00[/TD]
[/TR]
[TR]
[TD]FY18[/TD]
[TD]FEB[/TD]
[TD]China[/TD]
[TD]RLS[/TD]
[TD]MSW[/TD]
[TD]10004[/TD]
[TD]56[/TD]
[TD]$80.00[/TD]
[/TR]
[TR]
[TD]FY18[/TD]
[TD]MAR[/TD]
[TD]Vietnam[/TD]
[TD]RLE[/TD]
[TD]MSW[/TD]
[TD]10006[/TD]
[TD]32[/TD]
[TD]$76.00[/TD]
[/TR]
[TR]
[TD]FY18[/TD]
[TD]FEB[/TD]
[TD]Vietnam[/TD]
[TD]RLE[/TD]
[TD]MSW[/TD]
[TD]10006[/TD]
[TD]45[/TD]
[TD]$92.00[/TD]
[/TR]
[TR]
[TD]FY18[/TD]
[TD]MAR[/TD]
[TD]China[/TD]
[TD]RLC[/TD]
[TD]WSW[/TD]
[TD]10009[/TD]
[TD]29[/TD]
[TD]$97.00[/TD]
[/TR]
[TR]
[TD]FY18[/TD]
[TD]FEB[/TD]
[TD]China[/TD]
[TD]RLC[/TD]
[TD]WSW[/TD]
[TD]10009[/TD]
[TD]47[/TD]
[TD]$71.00[/TD]
[/TR]
[TR]
[TD]FY18[/TD]
[TD]MAR[/TD]
[TD]Thailand[/TD]
[TD]RLS[/TD]
[TD]WSW[/TD]
[TD]10008[/TD]
[TD]17[/TD]
[TD]$65.00[/TD]
[/TR]
[TR]
[TD]FY18[/TD]
[TD]FEB[/TD]
[TD]Thailand[/TD]
[TD]RLS[/TD]
[TD]WSW[/TD]
[TD]10008[/TD]
[TD]50[/TD]
[TD]$83.00[/TD]
[/TR]
[TR]
[TD]FY18[/TD]
[TD]FEB[/TD]
[TD]Singapore[/TD]
[TD]RLC[/TD]
[TD]CSW[/TD]
[TD]10003[/TD]
[TD]38[/TD]
[TD]$91.00[/TD]
[/TR]
</tbody>[/TABLE]

Is this feasible somehow?

 

[xenou]

Hi,

Your first table is not a valid Access table (it has empty cells in the top left corner and seemingly two column header rows which is not a valid table structure. Are you saying you want to import the data into Access from (possibly) Excel?

Also, the data is already pivoted in table 1 so it isn't clear why you would need to do this to pivot the data. Are you sure you need to do this in Access?

 

[esbencito]

You're right, I get the raw data in table 1 in an Excel file (it is not a pivot though). I have not yet tried to import it into Access, as I first want to figure out what's the best approach to transform the data in order to create a meaningful pivot table (which should then look something like table 1).

I currently cannot create a pivot table from table 1 unless I combine FY18 & MAR & SLS UNT into one column header, but then I wouldn't be able to filter by Year or Month anymore. So I need to somehow transform the data into table 2...

 

[RoryA]

You can use this trick in Excel: http://www.contextures.com/excelpivottablesourcedatafix.html.

What I usually do is to concatenate the row and/or column fields into just one row/column using a delimiter, normalise that data, then break out the parts of the original fields using the text to columns wizard.

 

[esbencito]

This super useful!! Thanks for sharing! Unfortunately, it won't work in my case, as the data exceeds 1,000,000 rows once it is transformed as seen in table 2, so it would have to be done in Access I guess

 

[xenou]

I think Excel could handle 1,000,000 rows, most likely. It doesn't exceed the row limit, in any case.