CrossTable + Generic Load but I am having issues sorting the data

[Nemo1]

Hello Everyone.. 

So I have the following table: (still the same problem as last week and the week before

I need to transpose it, so for every year I get a column, so it should look like this:

To do that, i did the following: 

1- CrossTable:

Data:
CrossTable("Attribute", Value, 1)
Load
"Production Cost",
Rent,
Manpower,
"Total Cost",
Sales,
Margin
FROM [Mycomputer...]
(ooxml, embedded labels, table is Sheet1);

2 - Generic Load:

Generic Load
"Attribute", // Sales, Costs, Store
Date, // Year-Month values like 2023-01, 2023-02
Value // The actual values for Sales, Costs, Store
Resident Data;

Which worked, the problem now is, that I need to sort the attributes differently (not like in the pic). From top to bottom, i should have them in this order: Total Cost, Sales, Production Cost, Rent, Margin. 

In order to sort them, in between the crosstable and the generic load, i added this code:

AttributeSortOrder:
Mapping Load * Inline [
"Total Cost",1
Sales,2
"Production Cost",3
Rent,4
Margin,5
];

DataWithSortOrder:
LOAD
"Attribute",
Date,
Value,
ApplyMap('AttributeSortOrder', Attribute, 999) as SortOrder // 999 is a default if there's no match
Resident Data
Order By SortOrder; // This ensures the data is sorted by the SortOrder field

I have two problems here:

1- For some reason, what is between the brackets in the AttributeSortOrder table is not highlighted. It is all dark, i dont know why.

2- When I load the data, it appears a "Field not found" for the DatawithShortOrder table..

what is it that i am doing wrong?

thanks

[Kushal_Chawda]

@Nemo1  why do you need generic load again once you do the cross table? After crosstable data format is properly structured data which you can add it into Pivot table to represent the way you want. In pivot table you can call Attribute from cross table into the Row dimension, Date in column dimension and measure as Sum(Value) which will give you expected output.

Below is the cross table script you need

Data:
CrossTable("Attribute", Value, 1)
Load

Date,
"Production Cost",
Rent,
Manpower,
"Total Cost",
Sales,
Margin
FROM [Mycomputer...]
(ooxml, embedded labels, table is Sheet1);

To sort the Attribute column , in sorting option of pivot table, you can sort the Attribute column by below expression

= match(only({1}Attribute),'Total Cost','Sales','Production Cost','Rent','Margin')