Cross Table - Excel with Many Columns

[microwin88x]

Hello,

I have the following table from an Excel:

PERIOD	HOTEL	ROOMS AVAILABLE BUDGET	ROOMS AVAILABLE ACTUAL	ROOMS OCUPPIED BUDGET	ROOMS OCCUPIED ACTUAL	SALES BUDGET	SALES ACTUAL
201807	Hotel1	10	10	10	10	10	10
201807	Hotel2	20	20	20	20	20	20
201807	Hotel3	30	30	30	30	30	30
201708	Hotel1	40	40	40	40	40	40
201708	Hotel2	50	50	50	50	50	50
201708	Hotel3	60	60	60	60	60	60
201709	Hotel1	70	70	70	70	70	70
201709	Hotel2	80	80	80	80	80	80
201709	Hotel3	90	90	90	90	90	90

The thing is that I have many columns (each one is repeated by Budget or Actual).

Is there any way to transpose the table in order to have something like this? (and would it be better to handle this way??)

PERIOD	HOTEL	METRIC	TYPE	VALUE
201807	Hotel1	ROOMS_AVAILABLE	BUDGET	10
201807	Hotel1	ROOMS_AVAILABLE	ACTUAL	10
201807	Hotel2	ROOMS_AVAILABLE	BUDGET	20
201807	Hotel2	ROOMS_AVAILABLE	ACTUAL	20
201807	Hotel3	ROOMS_AVAILABLE	BUDGET	30
201807	Hotel3	ROOMS_AVAILABLE	ACTUAL	30
201807	Hotel1	ROOMS_OCCUPIED	BUDGET	10
201807	Hotel1	ROOMS_OCCUPIED	ACTUAL	10
201807	Hotel2	ROOMS_OCCUPIED	BUDGET	20
201807	Hotel2	ROOMS_OCCUPIED	ACTUAL	20
201807	Hotel3	ROOMS_OCCUPIED	BUDGET	30
201807	Hotel3	ROOMS_OCCUPIED	ACTUAL	30
201807	Hotel1	SALES	BUDGET	10
201807	Hotel1	SALES	ACTUAL	10
201807	Hotel2	SALES	BUDGET	20
201807	Hotel2	SALES	ACTUAL	20
201807	Hotel3	SALES	BUDGET	30
201807	Hotel3	SALES	ACTUAL	30
201808	Hotel1	ROOMS_AVAILABLE	BUDGET	40
201808	Hotel1	ROOMS_AVAILABLE	ACTUAL	40
201808	Hotel2	ROOMS_AVAILABLE	BUDGET	50
201808	Hotel2	ROOMS_AVAILABLE	ACTUAL	50
201808	Hotel3	ROOMS_AVAILABLE	BUDGET	60
201808	Hotel3	ROOMS_AVAILABLE	ACTUAL	60
201808	Hotel1	ROOMS_OCCUPIED	BUDGET	40
201808	Hotel1	ROOMS_OCCUPIED	ACTUAL	40
201808	Hotel2	ROOMS_OCCUPIED	BUDGET	50
201808	Hotel2	ROOMS_OCCUPIED	ACTUAL	50
201808	Hotel3	ROOMS_OCCUPIED	BUDGET	60
201808	Hotel3	ROOMS_OCCUPIED	ACTUAL	60

Or would it be better to use it as I get it? (I only put 3 metrics for this demo, but I actually have like 7 or 😎

 Thanks in advance!!

[sunny_talwar · Accepted solution]

You can do something like this

Table:
CrossTable (METRIC, VALUE, 2)
LOAD * INLINE [
    PERIOD, HOTEL,  ROOMS AVAILABLE BUDGET ,  ROOMS AVAILABLE ACTUAL ,  ROOMS OCUPPIED BUDGET ,  ROOMS OCCUPIED ACTUAL , SALES BUDGET , SALES ACTUAL 
    201807, Hotel1,                                                       10,                                                             10,                    10,                                                        10,                    10,                    10
    201807, Hotel2,                                                       20,                                                             20,                    20,                                                        20,                    20,                    20
    201807, Hotel3,                                                       30,                                                             30,                    30,                                                        30,                    30,                    30
    201708, Hotel1,                                                       40,                                                             40,                    40,                                                        40,                    40,                    40
    201708, Hotel2,                                                       50,                                                             50,                    50,                                                        50,                    50,                    50
    201708, Hotel3,                                                       60,                                                             60,                    60,                                                        60,                    60,                    60
    201709, Hotel1,                                                       70,                                                             70,                    70,                                                        70,                    70,                    70
    201709, Hotel2,                                                       80,                                                             80,                    80,                                                        80,                    80,                    80
    201709, Hotel3,                                                       90,                                                             90,                    90,                                                        90,                    90,                    90
];

FinalTable:
LOAD PERIOD,
	 HOTEL,
	 Left(METRIC, Index(METRIC, ' ', -1)-1) as METRIC,
	 SubField(METRIC, ' ', -1) as TYPE,
	 VALUE
Resident Table;

DROP Table Table;