Handle Excel with Multiple 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	ROOM_SALES_BUDGET	ROOM_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	ROOM_SALES	BUDGET	10
201807	Hotel1	ROOM_SALES	ACTUAL	10
201807	Hotel2	ROOM_SALES	BUDGET	20
201807	Hotel2	ROOM_SALES	ACTUAL	20
201807	Hotel3	ROOM_SALES	BUDGET	30
201807	Hotel3	ROOM_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!!

[zzyjordan]

Hi, 

Try this script to load your data

tmpData:
CrossTable(Metrics,Value,2)

LOAD
PERIOD,
HOTEL,
" ROOMS_AVAILABLE_BUDGET ",
" ROOMS_AVAILABLE_ACTUAL ",
" ROOMS_OCUPPIED_BUDGET ",
" ROOMS_OCCUPIED_ACTUAL ",
" ROOM_SALES_BUDGET ",
" ROOM_SALES_ACTUAL "
FROM [lib://AttachedFiles/0109.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate

FinalData:
Load
PERIOD,
HOTEL,
Mid(Metrics,1,Index(Metrics,'_',2)-1) as "Metrics",
Mid(Metrics,Index(Metrics,'_',2)+1) as "Type",
Value
Resident tmpData;

drop Table tmpData;

Hope this helps

ZZ