Cross Join the tables and then outer Join

[Iras]

I have a table as below

Report	Area	Map	groceries PV	groceries AC	Electric PV	Electric AC	mail PV	mail AC
05_2016	DE	05_2016_DE	45	43	50	56	30	43
06_2016	ES	06_2016_ES	67	56	51	78	31	23
07_2016	FR	07_2016_FR	78	78	52	87	32	33
08_2016	BA	08_2016_BA	89	90	53	65	33	43

Based on the above table I would like to convert it into table as below: 

MapAttributePVAC

05_2016_DE	groceries	45	43
05_2016_DE	mail	30	43
05_2016_DE	Electric	50	56
06_2016_ES	groceries	67	56
06_2016_ES	mail	31	23
06_2016_ES	Electric	51	78
07_2016_FR	groceries	78	78
07_2016_FR	mail	32	33
07_2016_FR	Electric	52	87
08_2016_BA	groceries	89	90
08_2016_BA	mail	33	43
08_2016_BA	Electric	53	65

note: the result table is not shown completely

To do so I wrote logic as below in Data load editor and I am stuck with no output in the data model viewwe . Could some one please look into my code ? Or if you have any better solution to do the above process it will be great help:

the code is as follows:

List:
LOAD
Report,
Area,
"Map",
"groceries PV",
"groceries AC",
"Electric PV",
"Electric AC",
"mail PV",
"mail AC"
FROM [lib://AttachedFiles/Dummu.xlsx]
(ooxml, embedded labels, table is test);

PV:
load
Map,
sum("groceries PV") as GRS_PV,
sum("Electric PV") as ELE_PV,
sum("mail PV") as MA_PV,
sum("groceries PV") +sum("Electric PV") +sum("mail PV") as Total_PV
Resident List
group by Map;

PVUnpivot:
CROSSTABLE (ID,TypePV,1)
Load
Map,
GRS_PV,
ELE_PV,
MA_PV,
Total_PV
RESIDENT PV;
drop Table PV;

AC:
load
Map,
sum("groceries AC") as GRS_AC,
sum("Electric AC") as ELE_AC,
sum("mail AC") as MA_AC,
sum("groceries AC") +sum("Electric AC") +sum("mail AC") as Total_AC
Resident List
group by Map;

ACUnpivot:
CROSSTABLE (ID,TypeAC,1)
Load
Map,
GRS_AC,
ELE_AC,
MA_AC,
Total_AC
RESIDENT AC;
drop Table AC;

Costs:
load
*
Resident PVUnpivot;
outer Join load * Resident ACUnpivot;
drop table PVUnpivot;
drop table ACUnpivot;

Regards and thanks 

Iras

[Iras · Accepted solution]

 I found the solution.. Thanks alot guys