here is the load script i used . 1 main table and then 1 table for rows and 1 table for columns. Table concatenation to build the rows and columns table.
Data:
LOAD Category,
Customer,
Values
FROM
(ooxml, embedded labels, table is Sheet1);
//Columns
ColumnsForPivot:
Load
RecNo() as ColumnSortOrder,
Customer,
Customer as CustomerForPivot
Resident Data
order by Customer asc;
let vNumCustomers= NoOfRows('ColumnsForPivot');
Concatenate(ColumnsForPivot)
Load
$(vNumCustomers)+1 as ColumnSortOrder,
Customer,
'Total' as CustomerForPivot
Resident Data;
Concatenate(ColumnsForPivot)
Load
$(vNumCustomers)+2 as ColumnSortOrder,
Customer,
'% Share' as CustomerForPivot
Resident Data;
//Rows
RowsForPivot:
Load
RecNo() as RowSortOrder,
Category,
Category as CategoryForPivot
Resident Data
order by Category asc;
let vNumCategories= NoOfRows('RowsForPivot');
Concatenate(RowsForPivot)
Load
$(vNumCategories)+1 as RowSortOrder,
Category,
'Total' as CategoryForPivot
Resident Data;
Concatenate(RowsForPivot)
Load
$(vNumCategories)+2 as RowSortOrder,
Category,
'% Share' as CategoryForPivot
Resident Data;
